Performanceproblem, but where to look?
-
Hi there,
I have this situation: a large table(6 milj recs) is searched for records with a large & complex WHERE-qualifications. Records found are inserted into another table. This process takes 6+ minutes. On its own not a very large time, but I believe this process can be improved. So I'm testing with indices and such, de-normalizing to get rid of some very worrysome WHERE-clauses, but to no avail. Than I try something: I do a SELECT COUNT(*) with exactly the same WHERE-clause, and now this runs in 20 seconds!
Is this expected behaviour? Is a SELECT COUNT faster then a SELECT F1,F2,F3..., or have I missed something here? Is the actual fetching of the data the cause of the delay, and if so, how can 1 improve on this?
TIA,
Hans Brouwer
Greetz,
Hans Brouwer -
It might. Especially when selecting columns and inserting into another table, a lot of I/O is involved. Diskaccess is the one slowest thing of all in a database perspective.
In order to find the answer for a select count(*), it may be that SQL Server only has to count a suitable index (eg the PK), and not move a lot of bytes around, so this is expected to be much quicker.
+6 minutes om a 6 million row table suggest to me a table scan. If you can do anything about this largely depends on how the WHERE clause is constructed, and also how large the expected result set is. There's some operations that invalidates any indexusage and leaves you with a scan no matter what.
Try running the queries in QA and switch on the query plan - then it's easier to see what actually happens.
So.. it depends..
/Kenneth
-
Tnx for responding, Kenneth. I did check the query with Showplan, hence my adding indices and trying to add columns to denormalize the table to make it unneceserry to have LIKE and DATEPART in the WHERE-clause. Currently I have asked our hardwaresupport for the specs of our testserver, among other things to find out about diskspeed and such. Maybe something can be done about that. Then again, I did check with perfmon and EM on process and server activity, and I did not notice anything peculiar on disk io.
Tnx again,
Hans Brouwer
Greetz,
Hans Brouwer -
There are some things that will force table scans (thus rendering indices useless)
Such things as:
negations, leading wildcards, functions...
WHERE myCol <> 1
WHERE myCol NOT LIKE 'a%'
WHERE myCol LIKE '%a'
WHERE DATEDIFF(day, myDateCol, getdate()) > 1
etc...
Also, if you have functions in there, those may also affect performance in a severe way.
/Kenneth
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply