Indexing a Large Table

Question

Indexing a Large Table

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

roelofsleroux (2/2/2010)
As far as your opinion on the indexes goes, I'm pretty much happy with your statement that the Index on the composite key should be sufficient.

I did not say that.

I said that the cluster is on the date, the query is doing a clustered index seek and with the current way the query is written there is no way to get it better. That does not in the slightest imply that the index is sufficient, just that no indexing is going to help unless the query is rewritten.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 2

Hi

You say, if you don't use the date parameter query takes longer time. It means query takes the data set correctly to get the rows. So, the reason for taking about 5 minutes to process 8000 rows could be the function you are using. Is that function in turn, access much data? If so, you will make 8000 calls to determine the outcome. You may have to rewrite the function or if those values are static per stock, move to a table and join with that table.

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Rofty SSCrazy Points: 2585 More actions · Answer 3

Sorry GilaMonster, miss use of words. But i do get what you said.

Happens when I have 100s of things to do at the same time. 😀

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

roelofsleroux (2/2/2010)
Thank for the head-up. But will this explain the reason why my SQL server over-consumed hardware resources and ended up not responding?

If you have a lot of code or code that is used often written in the same fashion, you bet'cha.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 5

/*Thank for the head-up. But will this explain the reason why my SQL server

over-consumed hardware resources and ended up not responding? */

I guess I answered that sometime back.

Any investigation would start in this order -> Task manager->Perfmon->Sysprocesses->Queries.

Regards,
Raj

http://Strictlysql.blogspot.com