Re: SQL Slow from MSDE 2000 to SQL Express

13/09/2007, 19h12

HC wrote:
> I posted this in one of the VB forums but I'm starting to think it
> might be more appropriate to have it here, since it really seems to be
> a SQL server (MSDE/Express 2005) problem:
>
> Hey, all, I have a problem with queries against a SQL server instance
> that I just found and is causing me fits. I hope someone can point
> me
> in the right direction, please. TIA.
>
> Basically, I got a Vista OS machine to test my VB6 app on it as some
> of my clients will be switching over in the coming months. I went to
> a local Circuit City during early business hours in the middle of the
> week and I installed my application on each of 5 PC's on the new
> Vista
> OS (Tuesday, when it was released). I had read that MSDE 2000, which
> I normally use as my DB is not supported on Vista so I had downloaded
> and was using SQL Express 2005. Each system had at least a 1.9 GHz
> dual core processor and 1 GB of RAM. One process in my program finds
> records in one table that do not match records in another table and
> then reports those un-matched entries. On my development machine
> (laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHz
> Centrino (IIRC)) the process takes less than 30 seconds consistently.
> On each of those 5 systems at Circuit City the process took 5 minutes
> (on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes on
> each of two Gateway systems (the model numbers of which I forget at
> the moment). Each of these computers should be much faster than my
> laptop, and some had twice the RAM, and all had SATA or SATA II
> drives
> instead of my piddly 5400 laptop drive, I would have thought they'd
> all be faster but were abysmally slow.
>
>
> So, seeing a huge difference in the time, and to try to keep this
> short and sweet, I fired up another computer I have, running XP SP2,
> on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and my
> application and ran the process. < 30 seconds on each of multiple
> runs. Second, I unloaded MSDE 2000 and installed SQL Express 2005
> and
> moved the DB to it (sp_attach_db) which caused some upgrading
> (messages reported in OSQL about update/upgrade). When it was done I
> rebooted, to be sure, and the ran the program and the process again.
> On the same data, on the same computer, the process took 7-9 minutes
> consistently on each of several runs. This makes this part of the
> application unusable, and even the simple stuff like grabbing a
> single
> record from the DB (maybe 5 columns of no more than 500 bytes total)
> is noticeably slower on the SQL Express 2005 than on MSDE 2000.
>
>
> So, the problem seems to be with my interaction with the DB. I am
> using ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with a
> connection string like: Driver={SQL Server};server=(local)
> \caredata;database=caredata;Uid=sa; Pwd=<password>

Why are you using an ODBC connection string? OLEDB or the SQL Native
Client should be faster.

http://www.carlprothman.net/Default....erForSQLServer

<snip>

13/09/2007, 19h12	#1 (permalink)
Beowulf Messages: n/a Hébergeur:	Re: SQL Slow from MSDE 2000 to SQL Express HC wrote: > I posted this in one of the VB forums but I'm starting to think it > might be more appropriate to have it here, since it really seems to be > a SQL server (MSDE/Express 2005) problem: > > Hey, all, I have a problem with queries against a SQL server instance > that I just found and is causing me fits. I hope someone can point > me > in the right direction, please. TIA. > > Basically, I got a Vista OS machine to test my VB6 app on it as some > of my clients will be switching over in the coming months. I went to > a local Circuit City during early business hours in the middle of the > week and I installed my application on each of 5 PC's on the new > Vista > OS (Tuesday, when it was released). I had read that MSDE 2000, which > I normally use as my DB is not supported on Vista so I had downloaded > and was using SQL Express 2005. Each system had at least a 1.9 GHz > dual core processor and 1 GB of RAM. One process in my program finds > records in one table that do not match records in another table and > then reports those un-matched entries. On my development machine > (laptop with 1 GB of RAM, XP Pro SP2, MSDE 2000 (current SP), 2 GHz > Centrino (IIRC)) the process takes less than 30 seconds consistently. > On each of those 5 systems at Circuit City the process took 5 minutes > (on each of 3 HP machines, a1700n, a1720n, a1730n, and 11 minutes on > each of two Gateway systems (the model numbers of which I forget at > the moment). Each of these computers should be much faster than my > laptop, and some had twice the RAM, and all had SATA or SATA II > drives > instead of my piddly 5400 laptop drive, I would have thought they'd > all be faster but were abysmally slow. > > > So, seeing a huge difference in the time, and to try to keep this > short and sweet, I fired up another computer I have, running XP SP2, > on 512 MB RAM, AMD Athlon 2300+. First I loaded MSDE 2000 and my > application and ran the process. < 30 seconds on each of multiple > runs. Second, I unloaded MSDE 2000 and installed SQL Express 2005 > and > moved the DB to it (sp_attach_db) which caused some upgrading > (messages reported in OSQL about update/upgrade). When it was done I > rebooted, to be sure, and the ran the program and the process again. > On the same data, on the same computer, the process took 7-9 minutes > consistently on each of several runs. This makes this part of the > application unusable, and even the simple stuff like grabbing a > single > record from the DB (maybe 5 columns of no more than 500 bytes total) > is noticeably slower on the SQL Express 2005 than on MSDE 2000. > > > So, the problem seems to be with my interaction with the DB. I am > using ADO 2.8 in VB 6 (SP 6). I use DSN-less connections with a > connection string like: Driver={SQL Server};server=(local) > \caredata;database=caredata;Uid=sa; Pwd=<password> Why are you using an ODBC connection string? OLEDB or the SQL Native Client should be faster. http://www.carlprothman.net/Default....erForSQLServer <snip>

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email