Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-12 : 07:57:31
|
| ben writes "I have a 1.6 million row table (growing daily) that stores date based transactions for activity types and operators.I run a cursor against that table to identify activity for a given date range and, for each operator in turn, increment counters for each activity type and then write the values to a table variable.This is all wrapped in a function that returns the table.Four columns (including the activity date) are returned in the cursor and three of these (including the activity date) are used in its order by clause.Various indexation has been attempted - at the most extreme, a clustered index was created on the columns used in the order used. Regardless of the index type (clusterd / composite / non-clustered), and the columns used, the fastest response time is without any indexation. Why indexation not give a performance gain in retrieving and ordering the data - in gact, why does it make it substantially worse ?" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-08-12 : 08:06:17
|
| Posting some of your code would help us provide a better answer, but I doubt that the indexes are the root cause of the problem. In the first place, you're using cursors, they won't help performance at all, and likely won't use any indexes you place on the tables. The fact that not having indexes gives you the best performance is incidental, since the indexes are not being used by the cursor. Wrapping the code inside a function can also create extra overhead, depending on how the function is evaluated. The table variable may also be contributing to the performance decline, especially if it holds a lot of rows, since it cannot be indexed and can cause table scans if it should be joined to another table.In any case, the combination of the techniques you're using are the more likely culprit.It sounds like the result you're trying to get may be something like the following:SELECT Operator, Activity, Count(*) CounterFROM myTableWHERE DateCol BETWEEN '7/1/2005' AND '8/1/2005'GROUP BY Operator, ActivityIf that's what you're trying to achieve, then an index on the DateCol column would substantially improve the performance of this query. Indexes on the Operator and Activity columns would further improve the query by allowing the query optimizer to use index intersection more readily. |
 |
|
|
|
|
|
|
|
Functions, Cursors, Indices