• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

Slow MSSQL vs. ACCESS in VB ADODB

Help!

I have a vb app, that was running nice and smooth on an ACCESS database, using ADODB in code.

Now, I exported the tables, and moved it to an SQL database.

It's now running 5 - 10 times slower...

Pinging the server, and accessing it any other way on the network is just fine, it's only from accessing it within the VB app...

any thought?

I repeate... HELP!
0
shahzadq
Asked:
shahzadq
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Titan522Commented:
What does your execution plan look like? Can you run a query in query analyzer can compare the results?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>It's now running 5 - 10 times slower...
this is NOT the problem of SQL Server itself, but ususally missing indexes.
0
 
shahzadqAuthor Commented:
YEah, Query Analyzer is pulling them up just great.

Hmm ...   Indexes...  let me check something on that...  But wouldn't that also slow down Query Analyzer?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what network protocols are available for sql server (check the sql server network utility on the server, and the sql server client network utility on the client).
if the default is named pipes, change it to tcp/ip. possible disalbe named pipes completely if not needed
0
 
SStoryCommented:
Well are you using the SQL Server specific connection?  is the vb.net?  If so SQLclient works faster than OLEDB.

Connecting with the SQL specific connection lets SQL use Tabular Data Streaming (I think it is called).  This is a native format and makes it faster.

Of course if indexes aren't set, things will not be as fast as the could be.  Also if the query need improving it could slow things down.

Syntax between Access and SQL Server does differ some.

HTH,

Shane
0
 
shahzadqAuthor Commented:
OK, I think I'm at the best I can get

Your comments make sense...

The SQL database is poorly written (Hey, it was already setup when I got here).

But yes, I am using a specific connections string...  no it's not .Net,  I will check on the names pipes thing.

But I think the problem may simply be the machine it's running on.  Just too old.

Shahzad
0
 
SStoryCommented:
TCP/IP protocol would be better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now