Posted by Beowulf on 09/13/07 18:12

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.aspx?tabid=87#OLEDBProviderForSQLServer

<snip>

[Back to original message]