suggestions on fine tune performance (usage of sql search function)

[sirpelidor]

Hi, there's a "search" function at my asp web application. Because the function only searching ID (integer), the users require the search function has to be active at all time (meaning: if the ID is "1234", as soon as the user enter 1, it will display anything began with 1, when user type 12 it will display anything began with 12, so on .... until "1234")

I was able to got around that by mixing javascript + asp + ado combine with SQL call. Basically just have javascript listen when key is pressed, it then made asp kept sending sql statment with the LIKE clause (and i limit the display down to 15 records) to SQL server in order to display result in real time.

As you see, this is extremely draining performance, the connection between ASP and SQL server has to be always on while user is hitting button. They sure can't tell the different when doing data entry, but when I have more people doing Data Entry at the same time, the problem become obvious.

I'm looking for ways to accomplish the simpler result without putting so much stress on the connection to sql server.

Here's the thoughts I have, but not sure how exactly to implement it:

1) use Getrow to dump the recordset into a array, and have user search the array, but looping through a array is very slow

2) setup some kindda data structure, such as harshtable, or maybe binary-tree, and copy a recordset to that local linked-list. I think that probably the best idea, but i doubt scripting language such as ASP would support data-structure programming

3) .... re-write the whole application in asp.net and just use DataSet object off ado.net (well, that sure will require quite some development time)

4) come seek for advise from www.webdevelopers.com *wink*


Thank you for your time

[schizo]

I've got a couple of questions since nobody has given a reply...

1) Roughly how many people do you think will be searching your site at any one time?

2) You say you are limiting how many results are displayed, are you actually using the LIMIT statement in SQL?

3) Does this (probably uneeded) functionality really outweigh the importance of server speed?

[sirpelidor]

Hello schizo,
first of all, thank you for taking the time to read and reply.

1) within the office, probably 8 people do data entry max, the problem is when launch the application online, then the number of people doing data entry will be out of my hand.

however, the good thing is.... only that page really drain SQL server connection. Meaning there's a pretty high chance not everyone will be on that same page process data entry at the same time *crossing my finger*

2) yes, limit statement within sql

3) to me, no .... but my boss wanted such a function *roll*
but that doesn't stop me trying to convince him not to heheheh

After thoughts:
just came up this idea as I'm replying this post.... make a aspx page just to handle this search function (using DataSet), and have IIS run both ASP and ASP.net pages at the same time. Since its the same web server, routing from asp to aspx then route back to asp shouldn't be too hard (i hope). Hence, that shouldn't waste too much of development time.

[schizo]

Well the problem with the .NET method would be that you would have to query every row in the db to store it in the dataset (correct me if i'm wrong). So you would be querying countless rows that will probably never even be searched. Nevertheless the .NET approach does have a distinct advantage in that it won't require JavaScript (which means wider accessibility).

Really I don't think your original approach is all that bad though, but I have no idea what this application does. As long as your entire application is not centralized around people running searches, I don't think it will be a big deal.

[sirpelidor]

Hi schizo,

My issue with javascript + asp + ado -> sql is that: data entry people tend to spent quite some time in this one page doing data entry.

Often time they have a long list of IDs to key and i think it just me being not experience enough as a developer. I can't figure way to close the connection fast enough (like rs.close right after each key is being press or something) without running into run time errors.

My other best solution is to wait til there's enough complains from the users, then show my boss why this approach is bad practice, heheh

Thank you for your time :)