Since I normally only screw this up at the start of a project this was the second time I’ve done it but it’s an odd one. You’d kind of think that the fundamental nature of the limit clause would mean you’d need a consistent order for it to be useful. If you do a query that asks for the first 10 records and then another that’s identical but asks for the next 10 you probably assume that’s what you’d get. You have to remember this is SQL that the results returned are un-ordered unless specified. If your order by is not specific enough you can get a random set of results each time. Typically the same exact same query, even un-ordered returns the same results, but if you’re changing the limit clause it’s not an identical query and so the query plan may be subtly different and so you can’t be sure which records you’ll get. In fact your order by needs to specify the ordering predictably for every single row to get consistent results. An order by on a field that’s not distinct will still allow some degree of unpredictability and probably defeat the point of your limit on your query.
Archive for the ‘sql’ Tag
I’ve optimized a query to work better with dates on Advantage Database Server using DBF’s and I figured I ought to make a note of what I did so that I don’t forget the next time I need to. Of course I didn’t realise it was the dates that was causing the problems when I started, but that certainly appears to have been the case.
With DBF’s the date fields are stored as text so the trick when pulling the dates into another table is to convert them to text so that there’s no conversion take place. My original query to take a subset of a table was like this,
select top 30 rowid as row, [date], [time] into #records from whaudit order by [date], [time]
This took 1 minute with a fairly large dataset, even with an index of DToS(Date) + Time. By changing that to,
select top 30 rowid as row, convert([date], SQL_CHAR) as dt, [time] into #records from whaudit order by [date], [time]
It goes down to 3 seconds to execute on the same table. The use of the temporary table is to implement a paged query so that I can take a subset of records, and the rowid in ADS SQL is particularly useful for selecting the exact records.
The one weird thing is that if you alias the converted field [date] the query goes back to executing in a minute. I can’t quite figure that out, whether it’s because it’s because it’s the same column name as in the order by or because it’s a keyword but it gets a different execution plan and is really slow. This might not happen to all tables, but it certainly happens to ours with it’s specific index setup.
select top 30 rowid as row, convert([date], SQL_CHAR) as [date], [time] into #records from whaudit order by [date], [time]