February 11, 2012 at 7:28 pm
I'm moving a VB6 app that has been used with SQL 2000 to SQL 2008. It connect, is functional, but very slow. Things that would take less than a second take a minute. Reading various posts it looks like a different connection string would help but I havent been able to get anything else to work. Appreciate the help, thanks.
SQL 2000:
Private Sub Form_Load()
IniFileName = App.Path & "\" & "SqlServer.ini"
Dim MyServer As String
Dim MyValue As String * 20
MyServer = GetPrivateProfileString("Database", "Server", _
"No INI File Located", MyValue, _
Len(MyValue), IniFileName)
Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")
February 12, 2012 at 2:40 am
I don’t think it’s an issue with application (however it can be). Most of the times database upgrade 2000 -> 2008 performs slow if not properly done.
Can you please provide more information on database upgrade / migration? Who did it & what all steps you followed? How big the database is? etc.
February 12, 2012 at 2:43 am
This article would be helpful to you.
SQL Server 2000 to 2008 Upgrade - Microsoft
February 13, 2012 at 3:27 am
I would try running the queries you've got in your application in the database directly to see how fast they go--chances are this is a query optimisation issue thanks to changes in the optimiser between SQL 2000 and 2008, not a problem with VB6 itself; you'll need to rewrite the queries.
February 13, 2012 at 5:52 am
I'm sure the queries are running slow. Seems like they all are. I read if I change the driver, it would run faster.
I'm not much of a VB person, all I know is I have this connection m_con that gets opened and closed all over the place in the VB application so I have to keep it.
Old:
Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")
Trying to get to:
Set m_con = New ADODB.Connection
Call m_con.Open("DRIVER={SQL Server Native Client};SERVER=" & _
MyValue & ";UID=CustServ;PWD=xxxxxxxx")
I get an error that the driver is not found; think that is the 2008 driver? Also a lot of threads say going to that driver cured slowed stored procs, actually they got faster.
February 13, 2012 at 6:17 pm
I figured it out. I haven't used this app in years but it is being used still today. I haven't updated anything either. I installed the latest service pack for VB6 and added the ADO Data Control 6 to the app. Presto, runs fast.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply