slow data

[stevelibby]

hi
i am having trouble with a search query that takes to long and i cant understand why.
i am using a straight forward query governed by parameters and then using a getrows procedure i show the results, for some reason its not quick, the table itself is around 30000 records..
Can you help me understand why i lags!

[duckhunter]

Using GetRows, I'm assuming you are using Access? 30K rows in Access is kinda pushing the limits. Also, if there are multiple users hitting it at the same time, forget about it (scale up to MySQL or SQL Server).

Make sure your closing connections after you use them. Access is bad about memory leaks with orphaned connections.

What is the SQL Statement? Are your 'where' clauses on fields that are indexed? Are you using 'LIKE' in your SQL statement?

[stevelibby]

hi i use mssql, the query is 'where' and '=' not like. Would it be better if i filtered after getting the whole record set?
also it takes a second using enterprise manager.

[defanjos]

are you using indexes in your db?
if not, you probably should.

[stevelibby]

im not massively great with db, how will indexes help spped the retrival up?

[stevelibby]

im not massively great with db, how will indexes help speed the retrival up?

[defanjos]

I am no expert either, but I know you are supposed to have them, and they increase the speed of data retrieval, especially.

Do some searches for "mssql indexes" and "database indexes" - you'll get more info on them.

[LifeinAsia]

Yes, indexes should help. But if the query finishes in a second in Enterprise Manager, but takes a long time to run/be displayed through the web page, I suspect the underlying problem is something other than indexes. Most likely it's something else on the page that is causing the problem. But we need some more details about what is happening.

First, post your query so we see exactly what you're doing, then we can also suggest proper indexing.

Second, post the relevant parts of your code.

Third, let us know your basic configuration- version of MSSQL, version of Windows, type of application (ASP, PHP, etc.), dedicated or shared server, etc.

[duckhunter]

Also, what type of connection are you using? ie: in your connection string there is something like 'provider='. Reason I ask is that ODBC can be sluggish in certain situations.

[stevelibby]

Hi
I use Provider=sqloledb as mt connection string.
then for collection of data i use getrows() then a for next loop to present relevant data.

I have been reading up on indexes and if i have got it right it kind of groups records? so if you have 30000 records of the alphabet you can select that as an index and that will then group by letter, have i got that right?
If i have then lets move on...i already have a primary key, so how do i now create this index, i use enterprise manager.

[stevelibby]

it appears that creating an index in table seems to have speeded things up slightly, now if i have 2 related tables is it possibly to index these? or create query?