|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-27 : 13:51:34
|
Andrew writes "SQL 7.00.623, Server NT4 Service Pack 5, Client NT4 4.00.1381.
Have you seen a situation whereby the results from execution 1 of a SQL query can be different from execution 2,3,4,....30 of the same query, despite the underlying data not changing.
I'm executing a query through SQL Query Analyzer (problem also occurs in SQL Enterprise manager and a VB program accessing the database through ADO) and sometimes I get 14 rows returned (my source database has tables no larger than 2000 rows)...but I'm happy that the 14 rows being returned are right. But if I execute the exact same query (seconds later) a 2nd/ 3rd/ 4th/ 30th time....the number of rows returned may drop...some times down to 7 rows, sometiems down to 3...1....or worse from my point of view 0.....
I'm absolutely bamboozeled by this...my query works, but just not all the time...now while the query is complicated, it's not rocket science.
It's basically a query counting records and returning a GROUP BY set. A sample of the code and results are below.
a,b,c,d,e,i are related tables.
f is a time-period table with 5 records - T, T+1, T-1, T+2, T=2...where T represents Today, T+2 today plus 2 working days.
there's no database link between f and a,b,c,d,e,i...but i'm infering a relationship based on a date in table i and table f.
Calendar contains a record for every day in the year, with work/ non-work dates indentified...ie sat/sun/public holidays identified.
Query:SET DATEFORMAT dmy
SELECT a.FKVALIDUSERCODE AS a_FKVALIDUSERCODE, c.CODE AS c_CODE, COUNT(*) AS d_COUNT
FROM a, b, c, d, e, f, i
WHERE a.FKCODE = b.CODE AND a.ENDDATE = '31/12/2099 23:59:59' AND d.ENDDATE <= '31/12/2099 23:59:59'
AND b.CODE = d.FKCODE AND b.FKCODE = c.CODE AND d.FKID = e.ID AND e.FKCODE = 1
AND f.VERSION = 'P' AND f.id = 8 AND e.ID = i.FKID AND i.TYPE = 'OLD' AND (DATEDIFF(D,GETDATE(),i.MATURITYDATE) - (SELECT COUNT(*) FROM CALENDAR g WHERE e.FKCODE = g.FKCODE AND g.DAYINYEAR > GETDATE() AND g.DAYINYEAR < i.MATURITYDATE AND g.WORKDAY = 0) <= f.NUMBER1)
AND (DATEDIFF(D,GETDATE(),i.MATURITYDATE) - (SELECT COUNT(*) FROM CALENDAR h
WHERE e.FKCODE = h.FKCODE AND h.DAYINYEAR > GETDATE()
AND h.DAYINYEAR < i.MATURITYDATE AND h.WORKDAY = 0) >= f.NUMBER2)
AND a.FKUSERCODE IN (23249,88841,82670,28070,99999,99993,99990,99991,23249,87297,81454,84992,39474,87296,
86713,87522,83786,87743,84946,18056,19743,99999,99993,99990,99991)
GROUP BY a.FKVALIDUSERCODE, c.CODE
desired results come back as
a_FKVALIDUSERCODE c_CODE d_COUNT
----------------- ---------- -----------
99990 BMPOST 3
99991 BMREC 3
99993 BMSTAT 3
99999 COMPLETE 3
28070 ENTERINTST 2
82670 ENTERINTST 1
28070 ENTERMONEY 20
82670 ENTERMONEY 9
82670 ENTERVERIF 8
82670 LEADER 37
28070 OPICVERIFY 1
82670 OPICVERIFY 2
88841 PAYDISPLAY 7
23249 PAYVERIFY 3 (14 row(s) affected)
but repeating the execution of the query, can result in less than 14 rows being returned. the content of the result set is unpredictable...and it's that unpredictability that is killing me. effectively i've going to fill a grid with the answers above, the grid being indexed by columns 1 and 2.
I'm working in IRELAND, so there may be time-lapses in any communication, but I will check back. Also my e-mail address is live and valid, and i can reply via that. If this is not the right forum for something like this, please indicate so by return to my e-mail address.
The structure of my query is right for what I want to get back...I'm not worried about performance of same...more it's unpredicatability.
Regards,
Andrew" |
|
Query re-runs not giving same answer as 1st/previous runs - despite data not changing