Performanceproblem, but where to look?
-
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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply