Query speed and execution cost 1

[djj55]

Hello, SQL 2000
I have a query

SELECT AccountNumber, SUM(TransactionAmount) AS MedicalSum 
FROM Mytable 
WHERE Medical = 1 
    AND DateEntered = (SELECT MAX(DateEntered) FROM Mytable WHERE Medical = 1)
GROUP BY AccountNumber

It takes around 3 minutes to run, so I did an execution plan and found the the "Stream Aggregate/Aggregate" cost is 94%. Since the table only has 1.5 M records I did not think it should take that long.
I have indexes on accountnumber, medical and dateentered (note the PK is on a GID not setup by me).

Any suggestions on speeding this up?

Thanks for any help,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Can you post the output of this?

sp_helpindex 'MyTable'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

[tt]
IX_MyTable nonclustered located on PRIMARY AccountNumber
IX_MyTable_DateEntered nonclustered located on PRIMARY DateEntered
IX_MyTable_Medical nonclustered located on PRIMARY Medical
PK_MyTable clustered, unique, primary key located on PRIMARY GID
[/tt]
I hope this helps.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I think you will see better performance if you cover this query with an index. Something like this...

[tt]
Create Index IX_MyTable_AnyName On MyTable(DateEntered, AccountNumber, TransactionAmount, Medical)
[/tt]

If you have millions upon millions of rows in this table, creating the index may take a while, and will probably slow down your database while the index is being created. I encourage you to try this in a development environment first.

Also, please let me know if the performance is any better after creating this index.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should also mention that if this does NOT improve your performance, you should drop the index. Extra indexes on a table is bad for performance.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Thank you, I will try this and let you know. Just got pulled off to work on something else, so it may be tomorrow before I get back to this.

Thanks,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[djj55]

Thanks George! It works great.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

You said originally that it took 3 minutes to run. How long does it take now? Just curious.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Under a minute.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

When you look at the execution plan now, you should see an index scan or index seek. Which is it?

The reason I ask is because the order in which the columns are added to the index is very important. Originally, I suggested this ordering:

DateEntered, AccountNumber, TransactionAmount, Medical

But it's possible that an alternative ordering may be better. A lot of this depends on the actual data in your table. If you have the time, I encourage you to try this column ordering:

AccountNumber, DateEntered, TransactionAmount, Medical

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Hello George,
I had a chance to try the different index. DateEntered first took 39 seconds, AccountNumber first took 56 seconds.

Notice this was on the production server (shame on me) and could of had other influences.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Are you satisfied with the performance you are getting now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Yes.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!