Different result of each select statement

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Different result of each select statement

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-14 : 06:24:31

Hi, I execute a select statement

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'

every times I execute it I get different result.

1st result : 17036986
2nd result : 17037903
3rd result : 17038309

Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-03-14 : 06:53:26

do the results have diffrent outputs when you use the actually column name in the count function

SELECT COUNT(DTRECORD) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-16 : 21:03:54

Yes, I tried it, still get different results. Please advise.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-16 : 21:24:18

Hi Juicyapply - glad to hear you are using count(*) after the debate on other methods!
This is odd, but will not have anything to do with statistics. Statistics affect the query plans not the results. Can you run your DB in read-only mode to make double sure there is not any other activity?

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-16 : 21:46:59

quote:
Originally posted by LoztInSpace

Hi Juicyapply - glad to hear you are using count(*) after the debate on other methods!
This is odd, but will not have anything to do with statistics. Statistics affect the query plans not the results. Can you run your DB in read-only mode to make double sure there is not any other activity?

May I know how to run it in read-only mode? there are other activities running on it, which will insert daily data on the table. But I am selecting history records, it shouldn't get any affect right?


SELECT COUNT(*) FROM TABLEA (nolock) WHERE DTRECORD < '1-MARCH-2008'

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-17 : 01:40:55

I can't actually remember how to do start the DB read-only. Your DBA will have to help you out here.
Only you will be able to tell if your history is getting changed. The evidence would suggest that it is - different counts, getting bigger. Looks like someone is inserting to me.
I'd get rid of the nolock as well. That will give you all sorts of crap if other people are on the database.

Another approach would be to select your TABLEA PKs into another table to use as a baseline. Then you can periodically select those that are NOT IN your new table and you can investigate.

A final reason might be that your DTRECORD is not actually a date but a varchar and new data is coming in after your cut off but is getting in because of the character compare. With a date, you would expect the following to be true, but with a varchar it is not.

select case when '1-march-2008' < '10-march-2008' then 1 else 0 end;

vs

select case when convert(datetime, '1-march-2008') < convert(datetime,'10-march-2008') then 1 else 0 end;

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-17 : 08:56:20

The DTRECORD is in DATETIME datatype and DTRECORD is current date time, it should not have data pump in in this case. It is really strange....when there is heavy transaction on the table, the problem will happen.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-17 : 20:09:35

Are there any triggers? This sort of thing is why I hate triggers!

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-17 : 21:14:47

quote:
Originally posted by LoztInSpace

Are there any triggers? This sort of thing is why I hate triggers!

I have checked on it, there is no trigger. but got another job is running to insert data into TABLEA. DTRECORD value is get from getdate(). No idea at all!!!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-17 : 21:28:53

I am confused.. didn't you say there is another process running that is inserting data into this table? If so, then why do you expect that the counts would be the same?

- Jeff
http://weblogs.sqlteam.com/JeffS

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-17 : 21:54:08

quote:
Originally posted by jsmith8858

I am confused.. didn't you say there is another process running that is inserting data into this table? If so, then why do you expect that the counts would be the same?

- Jeff
http://weblogs.sqlteam.com/JeffS

because I only query the history records. The another process only inserts data with latest DTRECORD

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-17 : 22:15:45

Did you consider the most likely cause? The other process isn't doing what you think?

- Jeff
http://weblogs.sqlteam.com/JeffS

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-04-07 : 02:32:20

Hi all, any other idea?? I am still facing this problem and my program fails few times. The success time is only when that week do not has too much data pump in, so the table size is small. Then select count returns exact value....I have checked with others DBA, we confirm that there is no other process insert data with DTRecord earlier that current sys date.

Please help!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-07 : 10:48:12

Just for fun, try:

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '2008-03-01 00:00:00'

--
Lumbago
"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4"

tm
Posting Yak Master

160 Posts

Posted - 2008-04-07 : 15:03:26

quote:
Originally posted by LoztInSpace

I can't actually remember how to do start the DB read-only. Your DBA will have to help you out here.
Only you will be able to tell if your history is getting changed. The evidence would suggest that it is - different counts, getting bigger. Looks like someone is inserting to me.
I'd get rid of the nolock as well. That will give you all sorts of crap if other people are on the database.

Another approach would be to select your TABLEA PKs into another table to use as a baseline. Then you can periodically select those that are NOT IN your new table and you can investigate.

A final reason might be that your DTRECORD is not actually a date but a varchar and new data is coming in after your cut off but is getting in because of the character compare. With a date, you would expect the following to be true, but with a varchar it is not.

select case when '1-march-2008' < '10-march-2008' then 1 else 0 end;

vs

select case when convert(datetime, '1-march-2008') < convert(datetime,'10-march-2008') then 1 else 0 end;

Just wondering if you tried LoztInSpace suggestion another table as baseline? Sound a great idea.

If the table is not too large I would copy this table to another table TABLEA_TEMP and run the script a few times on this copied table and see if the results are the same. If the results are not the same then one of your processes is causing the problem, either inserting date incorrectly or updating the date.

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-04-07 : 22:26:41

please allow me to ask one funny question,

select * from tableA where dtrecord < '20080301'
AND
select * from tableA where dtrecord > = '20080301'
EQUAL TO
select * from tableA???

1.in first query, there are few scheduled job running to insert data with dtrecord = getdate(). I copy all data tableA with dtrecord < 1-march-2008 to tableX

2.in second query, no job running. I copy all data tableA with dtrecord > = 1-march-2008 to tableY

3.in third query, no job running. I select count(*) from tableA.

result I get, tableA count <> tableX and tableY

What should I do to find out which step has something wrong...?

DBA has done purging on tableA, so the size has become small. I won't have problem in this time. Failure will happen again when tableA growth to very huge size, around 8 digits of rows.

tm
Posting Yak Master

160 Posts

Posted - 2008-04-08 : 11:34:22

quote:
Originally posted by juicyapple

please allow me to ask one funny question,

select * from tableA where dtrecord < '20080301'
AND
select * from tableA where dtrecord > = '20080301'
EQUAL TO
select * from tableA???

1.in first query, there are few scheduled job running to insert data with dtrecord = getdate(). I copy all data tableA with dtrecord < 1-march-2008 to tableX

2.in second query, no job running. I copy all data tableA with dtrecord > = 1-march-2008 to tableY

3.in third query, no job running. I select count(*) from tableA.

result I get, tableA count <> tableX and tableY

What should I do to find out which step has something wrong...?

DBA has done purging on tableA, so the size has become small. I won't have problem in this time. Failure will happen again when tableA growth to very huge size, around 8 digits of rows.

You might want to try checking if dtrecord is NULL even though you mentioned it should default to getdate().
select * from tablea where dtrecords is null

But based on your original post .. IMHO there seems to be a process or stored proc that is updating or inserting incorrectly.

For debugging / Logging ..
You can try putting a insert and update trigger on DTRECORD and if it changes save the information into a log table with some fields to determine what record from TABLEA where changed and from orginal value (DTRECORD) to modified value.

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-04-09 : 11:15:07

[code]
For debugging / Logging ..
You can try putting a insert and update trigger on DTRECORD and if it changes save the information into a log table with some fields to determine what record from TABLEA where changed and from orginal value (DTRECORD) to modified value.
[/code]

a good idea to trace it..but if the trigger fails, will it affect the existing function? as I do not have test environment for this...

tm
Posting Yak Master

160 Posts

Posted - 2008-04-10 : 09:59:25

Yes, I do believe it will affect it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources