Hi,
I’ve tried the following query in SQL SERVER 2005, SQL Express 2005 and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN (‘B0E’,’B1P’,’B2H’,’B2Y’,’B3A’,’B3M’,’B4A’,’B4H’,’E1A’,’E1C’,’E1N’,’G0A’, ?)) and (substring(Telephone,1,3) IN (‘204′,’250′,’306′,’403′,’416′,’418′,’450′,’506′,’514′,’519′,’604′,’613′,’705′,’780′,’807′,’819′,’902′,’905’)))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field “PostalCode” and “Telephone”.
When I execute this query on MACCESS and in SQL Server 2000 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from table2 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN (‘B0E’,’B1P’,’B2H’,’B2Y’,’B3A’,’B3M’,’B4A’,’B4H’,’E1A’,’E1C’,’E1N’,’G0A’, ?)) and (substring(Telephone,1,3) IN (‘204′,’250′,’306′,’403′,’416′,’418′,’450′,’506′,’514′,’519′,’604′,’613′,’705′,’780′,’807′,’819′,’902′,’905’)))
the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server 2000) the execution time is less than half second.
MACCESS is much faster than Sql Express 2005 and Sql Server 2005 in this situation
I can change the structure of the query to make it faster. But i can`t change the structure of the query. But even so, it`s working fine in MACCESS and SQL 2000, so why not in Sql Server 2005 engine
Please Help Me!!!!
Anyone have an idea why? What difference in these two database engine make these execution time so different.