| Author |
Topic |
|
Bien
Starting Member
12 Posts |
 Posted - 2003-09-25 : 03:40:08
|
My table has a PK, 3 indexes and more then 50million records. The PK has 8fields (pd_CtrlEntity, pd_BkUnit, pd_GLCode, pd_ProdCode, pd_BaseCurr, pd_SourceCurr, pd_EffectiveDate, pd_JEGrpNumber, pd_JEGrpDate)Index 1 is pd_CtrlEntity, pd_JEGrpNumber, pd_EffectiveDateIndex 2 is pd_CtrlEntity, pd_BkUnit, pd_EffectiveDateIndex 3 is pd_CtrlEntity, pd_GLCode, pd_EffectiveDateWhen I use this statement:Select Top 300 * FROM GLtPostedDetails WITH (INDEX(PK_GLtPostedDetails)) WHERE pd_CtrlEntity = 'EBRB' and pd_Bkunit >= '0101' and pd_EffectiveDate >= '07/01/2001' and pd_EffectiveDate <= '07/07/2001' and pd_JEGrpNumber = 'JESI02109A' and pd_JEGrpDate = '07/04/2001'the records fetched are okay.When I use this:Select Top 300 * FROM GLtPostedDetails WHERE pd_CtrlEntity = 'EBRB' and pd_Bkunit >= '0101' and pd_EffectiveDate >= '07/01/2001' and pd_EffectiveDate <= '07/07/2001' and pd_JEGrpNumber = 'JESI02109A' and pd_JEGrpDate = '07/04/2001'It used index 2 and the records fetched are incomplete. What seems to be the problem?Please help me. Thanks!  ****  ****  **** |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-09-25 : 07:11:45
|
| What do you mean by "incomplete"? Do you not get a 300 rows in your rowset?Jay White{0} |
 |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-09-25 : 07:14:52
|
| It is because of ordering of data, which uses different indexes. Try count(*) instead Top 300, it should return equal counts Or try order by anycolumnSekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-25 : 09:28:05
|
| It's gotta be that damn miracle thing again....Did you bounce the box?What's the total length of the row?Do you let SQL Server allocate it's memory, or did you put an upper boundry to it?Are there multiple applications on the box?What interface are using to return your results? QA? VB? Java?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-25 : 20:51:18
|
quote:
Originally posted by Page47
What do you mean by "incomplete"? Do you not get a 300 rows in your rowset?Jay White{0}
I get 300rows but it returned the wrong set of results..for example: When I use the statement where I put the PK, the result is: A B C D E F G etc..But when I don't specify which index to use, the result is: A B K L M N etc..the records, C, D, E, F, G, H, I, J are missing.What seems to be the problem? Please help! Thanks! ---------------------------------------------------------**** **** **** |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-09-25 : 21:00:31
|
| Is it me, or is this caused by not having an order by on the queries??Oh Sekar already said this. I bet if you did an ORDER BY pd_CtrlEntity on your queries, it would come out right.Let us know!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-25 : 21:05:59
|
quote:
Originally posted by samsekar
It is because of ordering of data, which uses different indexes. Try count(*) instead Top 300, it should return equal counts Or try order by anycolumnSekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey.
Yup, if I put order by, the results are okay. but, I can't put order by in the select statement because it takes longer to fetch the records even though there are indexes..  **** **** **** |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-25 : 21:15:23
|
| Bien-There's no magic here:TOP WITHOUT AN ORDER BY CLAUSE IS MEANINGLESS.Index hint or not, you WILL NOT get consistent results using TOP without an ORDER BY. Sorry if I sound harsh, but it's the truth. It doesn't matter if it is slow or not, you need the ORDER BY clause if you need consistent results. |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-25 : 21:50:41
|
| robvolk, thanks. Now I know why I get inconsistent results. But, how does sql server get the results? If my records are physically ordered like this:ABCDEFGup to ZDoesn't it get the records based on the physical order? **** **** **** |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-25 : 22:14:20
|
| NO.Physical order is meaningless in a relational data store. Only the VALUE of a piece of data matters.Think of musical chairs. The people are the same, no matter which chair they sit in. Their names, hair color, weight, height, etc. do not change simply because they sit in another chair. Therefore, a row of data is the same no matter where it may be physically stored on the disk, or in relation to other rows of data. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-09-25 : 22:19:52
|
Great smileys Bien.Sam |
|
|
|
Bien
Starting Member
12 Posts |
Posted - 2003-09-26 : 01:59:31
|
| Okay... Another question: Assuming I would like to get the TOP 300 records only. Does sql server get all the records that satisfy a certain criteria (WHERE clause), then just take the TOP 300 records, or does it check row by row, then when it gets to the 300th record, it stops?I appreciate all your replies. Thanks. **** **** **** |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-09-26 : 06:37:09
|
| I think the former is true....get all records that satisfy the where condition and then only display the required amount. putting an "order by" gets the data into a predictable order (and to do that it has to process ALL the data) and then TOP reduces the amount of data returned to the user.In a way TOP is acting like a "SET ROWCOUNT x" statement."Sometimes (but not always)" they are swopable in the effects they can cause depending on the SQL statement that is used. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-26 : 09:54:49
|
| Ok...Good stuff...but I gotta ask...What's 300 out of 50 million?Isn't there a target population you want....It seems all pretty arbitrary to me...What are you really trying to do...In business terms...no tech talk...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
Incomplete records fetched