Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gchester
Starting Member
4 Posts |
 Posted - 2002-11-21 : 09:50:34
|
| Hi,Don't know if anyone can help or even if this is the right place to ask.I have an application that gets data from a SQL Server 7 data base via an ODBC Driver using DAO 3.51, and puts it into Excel to produce a report.I have two machines that I can run the same application on.The first is a dual 1GHZ Xeon with 512Kb Memory.The Second is a dual "GHZ Xeon with 2Mb Memory.As you would expect the application genarally runs alot quicker on the higher specced machine......HOWEVER............a certain part of the process actually runs 58% faster on the Low Spec Machine!!!!!The part of the process is a routine where I search through serveral open recordsetsets for the data I require and store it in an array.I cannot figure out why the High Spec machine does this part slower than the Low Spec Machine....Is there any DAO/Jet configuration that might be different on the second machine???Thanks in advance for any help offered!CheersGiles |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-11-22 : 02:55:58
|
| Hi!Is the SQL server on the same machine?Is the software the same on the machines (OS, excel etc)?Do you have any other apps running while testing? |
 |
|
|
gchester
Starting Member
4 Posts |
Posted - 2002-11-22 : 08:57:40
|
| Hi,I know what you are getting at, the SQL Server is on the High Spec Machine and I initially thought that this was the problem. BUT I tried running it with the SQL Server on the LOW spec machine and it made no difference.VB & Excel is locally installed on both machines and there is no other software running when I do the testing.The issue only seems to affect the application when I filter and find with the recordsetsets that have been opened.I really am stumped!! :-( |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
gchester
Starting Member
4 Posts |
Posted - 2002-11-22 : 10:38:43
|
| Unfortunately ADO is far too inflexible for what I am doing.The fact that it does not support AND in the Find method means I have to use filters for every search which doubles the run time and I also need to be able to handle NULL values efficiently which ADO does not lend it self to either. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-22 : 10:49:50
|
| Why not just pass the search conditions into the SELECT statement when you initially open the recordset? You'll get much better performance out of DAO and ADO and won't need the Find methods at all.And if you're not using CopyFromRecordset to populate the Excel sheet, you really should take a look at it, it's much faster than scrolling through a recordset object.BTW, which version of Excel are you using? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-22 : 10:58:54
|
| Yes, as Rob said, it may be faster to open a small recordset each time and just get what you need with exact criteria, as opposed to opening a full recordset and using FIND's over and over. - Jeff |
|
|
|
gchester
Starting Member
4 Posts |
Posted - 2002-11-25 : 06:31:41
|
| Thanks for your input guys.I am already filtering most of the unwanted records anyway and just performing the find statement on a very small recordset.Also getting the data into excel is not the issue as this is done in a different part of the application whih isnt affected by this performance issue (although I will look as the ideas you suggest for this)The real meat of the problem is why this part of the application, however efficient/ineffecient, takes 58% longer to run on a machine that is at least twice as powerful.As far as I can see it either has to be a problem with they installation/set-up of DAO/MS Jet or some suprious problem with the high spec machine.I guess I will never get to the bottom of it :-( I am even considering doing the data capture part of the application on the slower machine and calling it from the main app running of the faster machine! Which I am sure you will all agree I shouldn't have to do!Once again, thanks for you input. |
|
|
|
|
|
|
|
|
Strange ODBC/DAO Performance Issue