|
|
Posted by HC on 02/02/07 03:01
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>
I use the RecordsSet Object to open the data similar to this:
oRS.Open
strSQL$, oCN, adOpenKeyset
after the oCN object has had the connection string set and the object
is opened.
Considering that the same computer, against the same data, with the
same program, takes about 14 times (or more) longer to run, then it
has to be either that SQL Express 2005 is slow OR that my program is
interacting with it in an incorrect manner.
Can someone point me in the right direction, please?
Thank you.
--HC
So, the problem isn't Vista
Navigation:
[Reply to this message]
|