We've got a weird problem with joining tables from SQL Server 2005 and MS Access 2003.
我们在加入SQL Server 2005和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).
服务器上有一个大表,Access中有一个相当小的表。这些表通过3个字段连接起来,其中一个是日期时间字段(包含一天;想法是从大服务器表中获取其他数据(每日)以将数据添加到本地表)。
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.
直到周末,每天都很好。从昨天开始,我们通过此查询在Access中遇到了奇怪的非超时。非超时意味着查询将以相当高的网络传输永久运行,但不会发生超时。 Access甚至不显示进度条。服务器跟踪告诉我们在SQL服务器上反复执行相同的查询而没有错误但也没有结果。我们已经把它缩小到看似正在访问带有一个大表的服务器表以及包含日期的JOIN或WHERE的问题,但我们实际上无法缩小范围。我们已经重建了索引,目前正在恢复备份数据,但也许这里有人可以尝试任何指示。
Thanks, Mike.
6 个解决方案
#1
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.
如果您在Access中将本地表连接到SQL Server中的链接表,并且根据链接到链接数据的特定限制,查询实际上并不简单,那么Access很可能会从SQL Server中提取整个表并执行在本地加入整套。这是一个众所周知的问题。
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.
这并没有直接解决您提出的问题,但是您将所有数据放在一个地方(SQL Server)有多远?恕我直言,只要你在每个系统中都有一些数据,你就会发现同样类型的性能问题困扰着你。
If it were all in SQL Server a pass-through query would optimize and use available indexes, etc.
如果它全部在SQL Server中,则传递查询将优化并使用可用索引等。
#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).
如果server_table是一个大表(很难说,我们有150万行;测试表有10行左右),local_table是一个包含日期的单个单元格的表。这永远运行。这不仅是缓慢的,它似乎什么也没做 - 似乎 - 导致网络流量而且没有时间(这是我发现的那么奇怪;通常你得到一个超时,但这只是继续运行)。
We've just found KB article 828169; seems to be our problem, we'll look into that. Thanks for your help!
我们刚刚发现知识库文章828169;似乎是我们的问题,我们会调查一下。谢谢你的帮助!
#3
Use the DATEDIFF function to compare the two dates as follows:
使用DATEDIFF函数比较两个日期,如下所示:
' DATEDIFF returns 0 if dates are identical based on datepart parameter, in this case d
如果日期基于datepart参数相同,则DATEDIFF返回0,在本例中为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.
DATEDIFF针对日期进行了优化。如果其中一个日期为NULL,则比较等于(=)符号两侧的CONVERT函数的结果可能会导致表扫描。
Hope this helps,
希望这可以帮助,
Bill
#4
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 ?
尝试其他语法?类似于:SELECT * FROM BigServerTable b WHERE b.DateFld in(SELECT DISTINCT s.DateFld FROM SmallLocalTable s)问题描述中的奇怪之处是“直到周末这个每天运行良好”。这意味着问题确实存在于其他地方。您是否尝试创建新的空白Access数据库并从旧数据库导入所有内容?或者只是刷新所有链接?
#5
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
请发布执行此操作的查询,因为您拥有索引并不意味着它们将被使用。如果您的WHERE或JOIN子句不是sargable,则不会使用索引
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
看看BlkBy列,看看谁阻止了什么
#6
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).
只是一个想法,但在SQL Server中,您可以附加Access数据库并使用该表。然后,您可以在服务器上创建一个视图,以在SQL Server中进行全部连接。知识库文章中提出的解决方案对我来说似乎有问题,因为它是一个kludge(如果LIKE工作,那么=也应该)。
If my suggestion works, I'd say that it's a more robust solution in terms of maintainability.
如果我的建议有效,我会说它在可维护性方面是一个更强大的解决方案。
#1
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.
如果您在Access中将本地表连接到SQL Server中的链接表,并且根据链接到链接数据的特定限制,查询实际上并不简单,那么Access很可能会从SQL Server中提取整个表并执行在本地加入整套。这是一个众所周知的问题。
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.
这并没有直接解决您提出的问题,但是您将所有数据放在一个地方(SQL Server)有多远?恕我直言,只要你在每个系统中都有一些数据,你就会发现同样类型的性能问题困扰着你。
If it were all in SQL Server a pass-through query would optimize and use available indexes, etc.
如果它全部在SQL Server中,则传递查询将优化并使用可用索引等。
#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).
如果server_table是一个大表(很难说,我们有150万行;测试表有10行左右),local_table是一个包含日期的单个单元格的表。这永远运行。这不仅是缓慢的,它似乎什么也没做 - 似乎 - 导致网络流量而且没有时间(这是我发现的那么奇怪;通常你得到一个超时,但这只是继续运行)。
We've just found KB article 828169; seems to be our problem, we'll look into that. Thanks for your help!
我们刚刚发现知识库文章828169;似乎是我们的问题,我们会调查一下。谢谢你的帮助!
#3
Use the DATEDIFF function to compare the two dates as follows:
使用DATEDIFF函数比较两个日期,如下所示:
' DATEDIFF returns 0 if dates are identical based on datepart parameter, in this case d
如果日期基于datepart参数相同,则DATEDIFF返回0,在本例中为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.
DATEDIFF针对日期进行了优化。如果其中一个日期为NULL,则比较等于(=)符号两侧的CONVERT函数的结果可能会导致表扫描。
Hope this helps,
希望这可以帮助,
Bill
#4
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 ?
尝试其他语法?类似于:SELECT * FROM BigServerTable b WHERE b.DateFld in(SELECT DISTINCT s.DateFld FROM SmallLocalTable s)问题描述中的奇怪之处是“直到周末这个每天运行良好”。这意味着问题确实存在于其他地方。您是否尝试创建新的空白Access数据库并从旧数据库导入所有内容?或者只是刷新所有链接?
#5
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
请发布执行此操作的查询,因为您拥有索引并不意味着它们将被使用。如果您的WHERE或JOIN子句不是sargable,则不会使用索引
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
看看BlkBy列,看看谁阻止了什么
#6
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).
只是一个想法,但在SQL Server中,您可以附加Access数据库并使用该表。然后,您可以在服务器上创建一个视图,以在SQL Server中进行全部连接。知识库文章中提出的解决方案对我来说似乎有问题,因为它是一个kludge(如果LIKE工作,那么=也应该)。
If my suggestion works, I'd say that it's a more robust solution in terms of maintainability.
如果我的建议有效,我会说它在可维护性方面是一个更强大的解决方案。