Dynamic Query with SQL Server

[BrianS]

We have just converted our Access DB backend from Jet to SQL Server Express.
For the most part everything has worked fine, and we've seen the performance
improvements we expected (hoped for), with one exception. We have a dynamic
query-by-report tool that let's the user select various options in order to
query data and view in a datasheet/export/run a report. It's fairly complex
query-generating code, because of all the options available. The performance
on it post-transition is very slow, and we believe it's because the
transaction is happening on the application side rather than the server, and
SQL via ODBC doesn't handle that structure as quickly as the native Jet
backend did. We need some ideas and guidance on how best to address this.
We're at a loss right now, and don't have the SQL experience to know the best
way to build those transactions on the server side.

 

[kingston via AccessMonster.com]

Is your front-end an ADP or MDB (with linked tables)? If it is an MDB, it
might be easier to troubleshoot inefficiency with the data structure, if
indeed that is the problem. In other words, modify your query bit by bit;
drop off output fields and tables one by one to see if perhaps a calculation
or a join is the issue. If it is a join, check the joined fields, and if
there isn't an index on a field, try creating one.

I assume you've walked through your code in debug mode to pinpoint that it is
the query execution that is slowing things down. Do you have other SQL
executions in code that don't exhibit this behavior? If so, I doubt that the
problem is the way you're connecting to the back-end or your code, assuming
you're doing things the same way. hth

 

[Dale Fye]

You might also want to copy the SQL of the dynamic SQL statement you are
generating, then paste it into SQL Server Express and run it, make sure it is
running OK, then check the query plan to determine where the bottleneck is.
You may just need to add an index or two.

Dale