Query hangs with INNER JOIN on datetime field

Question

Hi everybody!

We've got a weird problem with joining tables from SQL Server 2005 and MS Access 2003.

There's a big table on the server and a rather small table locally in Access. The tables are joined via 3 fields, one of them a datetime field (containing a day; idea is to fetch additional data (daily) from the big server table to add data to the local table).

Up until the weekend this ran fine every day. Since yesterday we experienced strange non-time-outs in Access with this query. Non-time-out means that the query runs forever with rather high network transfer, but no timeout occurs. Access doesn't even show the progress bar. Server trace tells us that the same query is exectuted over and over on the SQL server without error but without result either. We've narrowed it down to the problem seemingly being accessing server table with a big table and either JOIN or WHERE containing a date, but we're not really able to narrow it down. We rebuilt indices already and are currently restoring backup data, but maybe someone here has any pointers of things we could try.

Thanks, Mike.

Answer 1

Please post the query that is doing this, just because you have indexes doesn't mean that they will be used. If your WHERE or JOIN clause is not sargable then the index will not be used

take this for example

WHERE CONVERT(varchar(49),Column,113) = CONVERT(varchar(49),OtherColumn,113)

that will not use an index

or this

WHERE YEAR(Column) = 2008

Functions on the left side of the operator (meaning on the column itself) will make the optimizer do an index scan instead of a seek because it doesn't know the outcome of that function

We rebuilt indices already and are currently restoring backup data, but maybe someone here has any pointers of things we could try.

Access can kill many good things....have you looked into blocking at all

run

exec sp_who2

look at the BlkBy column and see who is blocking what

Answer 2

Thanks for your quick answer!

The actual query is really huge; you won't be happy with it :)

However, we've narrowed it down to a simple:

SELECT * FROM server_table INNER JOIN access_table ON server_table.date = local_table.date;

If the server_table is a big table (hard to say, we've got 1.5 million rows in it; test tables with 10 rows or so have worked) and the local_table is a table with a single cell containing a date. This runs forever. It's not only slow, It just does nothing besides - it seems - causing network traffic and no time out (this is what I find so strange; normally you get a timeout, but this just keeps on running).

We've just found KB article 828169; seems to be our problem, we'll look into that. Thanks for your help!

Answer 3

Just an idea, but in SQL Server you can attach your Access database and use the table there. You could then create a view on the server to do the join all in SQL Server. The solution proposed in the Knowledge Base article seems problematic to me, as it's a kludge (if LIKE works, then = ought to, also).

If my suggestion works, I'd say that it's a more robust solution in terms of maintainability.

--
David W. Fenton
David Fenton Associates

Answer 4

Hi,

Use the DATEDIFF function to compare the two dates as follows:

' DATEDIFF returns 0 if dates are identical based on datepart parameter, in this case d

WHERE DATEDIFF(d,Column,OtherColumn) = 0

DATEDIFF is optimized for use with dates. Comparing the result of the CONVERT function on both sides of the equal (=) sign might result in a table scan if either of the dates is NULL.

Hope this helps,

Bill

Answer 5

Try another syntax ? Something like:
SELECT * FROM BigServerTable b WHERE b.DateFld in (SELECT DISTINCT s.DateFld FROM SmallLocalTable s)
The strange thing in your problem description is "Up until the weekend this ran fine every day".
That would mean the problem is really somewhere else.
Did you try creating a new blank Access db and importing everything from the old one ?
Or just refreshing all your links ?

Answer 6

If you join a local table in Access to a linked table in SQL Server, and the query isn't really trivial according to specific limitations of joins to linked data, it's very likely that Access will pull the whole table from SQL Server and perform the join locally against the entire set. It's a known problem.

This doesn't directly address the question you ask, but how far are you from having all the data in one place (SQL Server)? IMHO you can expect the same type of performance problems to haunt you as long as you have some data in each system.

If it were all in SQL Server a pass-through query would optimize and use available indexes, etc.