MS SQL Server - SQL server slow with ODBC but not Query Analyzer

Author

SQL server slow with ODBC but not Query Analyzer

plaster1@gmail.com

2006-11-21, 7:13 pm

I Have a windows 2000 server running an SQL database. An XP client
interacts very slowly (15+ seconds per query) with the SQL database,
whereas other computers of various windows OS (win2k, win 98) can
interact with the database very quickly. I have researched for a few
days and found many similar (if not the exact same) problems in the
groups, but have found no answer, although a downgrade of the XP
client's MDAC to the 2000 server's version was suggested, but not
attempted. Here's some more info:

--the client side foxpro application i'm using uses an ODBC data
source. The DSN settings are the same between all client

cpu's using the application.
--MDAC version for server and win2k client: 2.5 SP3 --For XP client:
2.8 --win98: even older maybe 2.1?
--I can browse shared network drives over the LAN without any delay on
XP.
--I did attempt to modify the HOSTS file on the server to include my
client IP and computer name to no avail (as well as LMHOSTS same
effect)
--I installed SQL Server Query Analyzer on XP client and queries have
no delay (practically instantaneous).

Since the query analyzer doesn't lag--but foxpro app using the ODBC DSN
does--is it safe to assume that the problem is revolving around ODBC?

THanks,

BP

other related message threads:

http://groups.google.com/group/micr...e9fb2e
1d

http://groups.google.com/group/micr...488b67
b2

http://groups.google.com/group/comp...0895af
20

David Browne

2006-11-21, 7:13 pm

<plaster1@gmail.com> wrote in message
news:1164150005.123890.278500@b28g2000cwb.googlegroups.com...
>
> I Have a windows 2000 server running an SQL database. An XP client
> interacts very slowly (15+ seconds per query) with the SQL database,
> whereas other computers of various windows OS (win2k, win 98) can
> interact with the database very quickly. I have researched for a few
> days and found many similar (if not the exact same) problems in the
> groups, but have found no answer, although a downgrade of the XP
> client's MDAC to the 2000 server's version was suggested, but not
> attempted. Here's some more info:
>
> --the client side foxpro application i'm using uses an ODBC data
> source. The DSN settings are the same between all client
>
> cpu's using the application.
> --MDAC version for server and win2k client: 2.5 SP3 --For XP client:
> 2.8 --win98: even older maybe 2.1?
> --I can browse shared network drives over the LAN without any delay on
> XP.
> --I did attempt to modify the HOSTS file on the server to include my
> client IP and computer name to no avail (as well as LMHOSTS same
> effect)
> --I installed SQL Server Query Analyzer on XP client and queries have
> no delay (practically instantaneous).
>
> Since the query analyzer doesn't lag--but foxpro app using the ODBC DSN
> does--is it safe to assume that the problem is revolving around ODBC?
>

No, Query Analyzer uses ODBC, so I think you can rule that out. That leaves
query parameterization and SET options.

You should run Profiler on the server and capture the query from the FoxPro
client. You should also capture the execution plan, but the query is the
main thing. Look at how the query is parameterized, and look at all the SET
options for the connection. Both parameterization and SET options can
affect the query plan. Then run the query in QA exactly as it appears in
the Profiler after setting the SET options to match what FoxPro is using.

David

Dan Guzman

2006-11-22, 12:12 am

> Since the query analyzer doesn't lag--but foxpro app using the ODBC DSN
> does--is it safe to assume that the problem is revolving around ODBC?

Query Analyzer also uses ODBC. You might check to see if ODBC tracing is
turned on, though. That would explain the slowness.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<plaster1@gmail.com> wrote in message
news:1164150005.123890.278500@b28g2000cwb.googlegroups.com...
>
> I Have a windows 2000 server running an SQL database. An XP client
> interacts very slowly (15+ seconds per query) with the SQL database,
> whereas other computers of various windows OS (win2k, win 98) can
> interact with the database very quickly. I have researched for a few
> days and found many similar (if not the exact same) problems in the
> groups, but have found no answer, although a downgrade of the XP
> client's MDAC to the 2000 server's version was suggested, but not
> attempted. Here's some more info:
>
> --the client side foxpro application i'm using uses an ODBC data
> source. The DSN settings are the same between all client
>
> cpu's using the application.
> --MDAC version for server and win2k client: 2.5 SP3 --For XP client:
> 2.8 --win98: even older maybe 2.1?
> --I can browse shared network drives over the LAN without any delay on
> XP.
> --I did attempt to modify the HOSTS file on the server to include my
> client IP and computer name to no avail (as well as LMHOSTS same
> effect)
> --I installed SQL Server Query Analyzer on XP client and queries have
> no delay (practically instantaneous).
>
> Since the query analyzer doesn't lag--but foxpro app using the ODBC DSN
> does--is it safe to assume that the problem is revolving around ODBC?
>
> THanks,
>
> BP
>
>
> other related message threads:
>
> http://groups.google.com/group/micr...e9fb2e
1d
>
> http://groups.google.com/group/micr...488b67
b2
>
> http://groups.google.com/group/comp...0895af
20
>

bp.gabriel2006@gmail.com

2006-11-29, 7:13 pm

Thanks to both of you for your suggestions.

--I checked odbc tracing and that's turned off.

Haven't had time to try Profiler yet, but i'm skeptical of query
parameterization and SET options causing the issue(s), since the exact
same foxpro application works quickly on older cpu's with older windows
builds using the same server. I wonder if i'm missing something in XP
pro's data handling...

Note for other users:

Out of curiosity I did an ODBC trace. Here's the steps on how to do it
for anybody that wants to know:

1. Launch the ODBC Data Source Administrator by going to Start |
Settings | Control Panel | Administrative Tools | Data Sources (ODBC).

2. Select the Tracing tab.

3. Edit the Log File Path and provide a complete path to a new file
(e.g. C:\ODBC_Trace.log).

4. Check the box "Machine-Wide tracing for all user identities" if it
exists.

5. Click the Start Tracing Now button. Click OK to close the ODBC Data
Source Administrator.

6. Restart the application and reproduce the problem or error message
that you wish to capture in the ODBC trace.

7. After reproducing the problem, reopen the ODBC Data Source
Administrator.

8. Select the Tracing tab, click the Stop Tracing Now button.

9. View the log at the above mentioned path.

plaster1@gmail.com

2006-12-04, 7:12 pm

After much deliberation I discovered that I needed to switch to named
pipes. Here's how that's done.

go to control panel>>Administrative Tools>>Data Sources

in my case I select my custom DSN called MSSQL_VLP for Visual Lab Pro
LIMS application and clicked configure. On page two I clicked "Client
Configuration" and selected the radio button next to "Named Pipes".
The pipe name was the same as the server name in this case.

Sponsored Links