|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Upsized SQLServer app 100x slower than MsAccessbackend via ODBC. The app uses CRecordset MFC classes to access the db tables. After upsizing to SQLServer, the process of opening the database and recordsets takes 75seconds as compared with 0.5seconds in Access! I am amazed, and figure something fundamental is wrong. I use open type of CRecordSet::snaphot in the CRecordset::Open calls. If I use dynaset instead of snapshot it speeds things up a lot, but then I get problems down theline, fundamentally because I don't think the ODBC driver supports dynasets. I have added clustered index to the primary key of every table in SQL, and this goes a small way to improving performance but not much. I still think I have missed something fundamental - I am a newbie to SQLServer so go easy! :-) Many thanks for any help. Mark *** Sent via Developersdex http://www.developersdex.com *** Hi
You don't say if you connected with ODBC to your access database. You may want to check if ODBC tracing is on. Having client side cursors will mean all data is returned to the client before being displayed,which may slow things up. You may want to use SQL Profiler to soo what queries are being sent to the SQL server and either look at the query plans or feed it into the Index tuning wizard to try and improve it. John Show quote "Mark Baker" wrote: > > I have a Visual C++ Front-end application accessing a MsAccess databases > backend via ODBC. > > The app uses CRecordset MFC classes to access the db tables. > > After upsizing to SQLServer, the process of opening the database and > recordsets takes 75seconds as compared with 0.5seconds in Access! I am > amazed, and figure something fundamental is wrong. > > I use open type of CRecordSet::snaphot in the CRecordset::Open calls. If > I use dynaset instead of snapshot it speeds things up a lot, but then I > get problems down theline, fundamentally because I don't think the ODBC > driver supports dynasets. > > I have added clustered index to the primary key of every table in SQL, > and this goes a small way to improving performance but not much. > > I still think I have missed something fundamental - I am a newbie to > SQLServer so go easy! :-) > > Many thanks for any help. > Mark > > *** Sent via Developersdex http://www.developersdex.com *** > Hi John,
Thanks for the propmt reply. Here goes... The Access version also used ODBC. Sorry - not sure what you mean by ODBC tracing or how to turn on/off/heck if on - could you explain please. I switched on the SQL Profiler and for example the query below is executed to open one of the recordsets. The table has 40,000 entries, so not huge, but the query takes 10seconds (for this one table alone!). I guess the query is inefficient as it selects all, but I guess my main worry is, even if I optimised this query, there are other queries that will be used after the recordset is open, for example to display all entries from this table, and that will be just as slow. I guess the main point is that Access executes the same queries over 100x faster, and I can't understand why SQLServer would be slower than Access. Is there any fundamental reason why I shouldn't use CRecordset snapshots in SQLServer specifically (hopefully not or I am in for a major redesign :-( ) Here is the query from the profiler... declare @P1 int set @P1=31 exec sp_prepexec @P1 output, NULL, N'SELECT "ID","SpareAmt1","SpareAmt2","SpareAmt3","SpareAmt4","SpareAmt5","SpareA mt6","SpareAmt7","SpareAmt8","SpareText1","SpareText2","SpareText3","Spa reText4","SpareText5","SpareText6","SpareText7","SpareText8","SpareVal1" ,"SpareVal2","SpareVal3","SpareVal4","SpareVal5","SpareVal6","SpareVal7" ,"SpareVal8","SpareDate1","SpareDate2","SpareDate3","SpareDate4","SpareD ate5","SpareDate6","SpareDate7","SpareDate8" FROM "xSPARE_2"' select @P1 You mention optimising indexes etc, using the Index Tuning wizard, and maybe looking at Quey Plans. Could you possibly point me in the right direction where to find out about these. Many thanks *** Sent via Developersdex http://www.developersdex.com *** Hi
You can find ODBC tracing under one of the tabs in the ODBC applet in control panel/administrion menu. As David mentioned if you are not using the features of SQL server such as stored procedures and just sending preparing ad-hoc queries and then dropping them, your performance will not be great. You may want to go half way and use sp_executesql. You may want to read http://msdn.microsoft.com/SQL/sqlperf/default.aspx?pull=/msdnmag/issues/02/07/datapoints and related links. For instance this has an ADO slant, but alot of the points are general http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/ScaleNetChapt12.asp To learn about Query Plans in Index tuning the best place to start will be books online where there is a wealth of information. What you have to remember is that you are now using a totally different beast as your database engine. There can be alot more to upgrading than a simple wizard can (usually) cover, that is just a quick start first step. Otherwise you may have been better leaving the database alone and just throwing better hardware at it! HTH John Show quote "Mark Baker" wrote: > > > Hi John, > Thanks for the propmt reply. Here goes... > > The Access version also used ODBC. > Sorry - not sure what you mean by ODBC tracing or how to turn > on/off/heck if on - could you explain please. > > I switched on the SQL Profiler and for example the query below is > executed to open one of the recordsets. The table has 40,000 entries, so > not huge, but the query takes 10seconds (for this one table alone!). > I guess the query is inefficient as it selects all, but I guess my main > worry is, even if I optimised this query, there are other queries that > will be used after the recordset is open, for example to display all > entries from this table, and that will be just as slow. > > I guess the main point is that Access executes the same queries over > 100x faster, and I can't understand why SQLServer would be slower than > Access. Is there any fundamental reason why I shouldn't use CRecordset > snapshots in SQLServer specifically (hopefully not or I am in for a > major redesign :-( ) > > Here is the query from the profiler... > declare @P1 int > set @P1=31 > exec sp_prepexec @P1 output, NULL, N'SELECT > "ID","SpareAmt1","SpareAmt2","SpareAmt3","SpareAmt4","SpareAmt5","SpareA > mt6","SpareAmt7","SpareAmt8","SpareText1","SpareText2","SpareText3","Spa > reText4","SpareText5","SpareText6","SpareText7","SpareText8","SpareVal1" > ,"SpareVal2","SpareVal3","SpareVal4","SpareVal5","SpareVal6","SpareVal7" > ,"SpareVal8","SpareDate1","SpareDate2","SpareDate3","SpareDate4","SpareD > ate5","SpareDate6","SpareDate7","SpareDate8" FROM "xSPARE_2"' > select @P1 > > You mention optimising indexes etc, using the Index Tuning wizard, and > maybe looking at Quey Plans. Could you possibly point me in the right > direction where to find out about these. > Many thanks > > *** Sent via Developersdex http://www.developersdex.com *** > Many thanks John,
You've been extremely helpful. I will follow up those links now. Re sticking with Access, the main reason for moving on is that database corruptions are all too common in Access with several (say > 10) users; also Access is easily corrupted if the server is reset whilst client app still running (belive me they do it!). So I wanted to offer a more robust solution using SQLServer to combat these shortcommings in Access. I guess it looks like I've got my work cut out. *** Sent via Developersdex http://www.developersdex.com *** Hi
SQL Server is certainly a more robust solution, as it is written as a multi-user networked system, and will take you far beyond the capacity of access. John Show quote "Mark Baker" wrote: > Many thanks John, > You've been extremely helpful. I will follow up those links now. > > Re sticking with Access, the main reason for moving on is that database > corruptions are all too common in Access with several (say > 10) users; > also Access is easily corrupted if the server is reset whilst client app > still running (belive me they do it!). > > So I wanted to offer a more robust solution using SQLServer to combat > these shortcommings in Access. > > I guess it looks like I've got my work cut out. > > *** Sent via Developersdex http://www.developersdex.com *** > If you don't intend to rewrite your data-access code to take advantage
of the client-server model then you may well be better off sticking with a file-server database like Access. The point of a client-server database is to avoid performing lots of operations in client recordsets. SQL Server is optimized for set-based operations. Aim to move your business logic and data access code into the database layer using TSQL stored procedures. -- David Portas SQL Server MVP -- Yikes.
Thanks for the honest answer. That's probably the worst news I could have had today! I guess it means that SQLServer is inherently slower than Access for client recordset operations. If this is the case (and I can see the logic) why do Microsoft provide an upsizing wizard at all? Thanks Mark *** Sent via Developersdex http://www.developersdex.com *** Mark,
In my software company, we initially used Access as the backend database. Eventually we upsized to SQL Server but supported both versions for a while (until we realized that Access was evil and dumped it! Corruption, etc. Probably the same reasons why everyones leave it.) Before we upsized, we used only ad-hoc queries (what else is there?) and continue to only use them today. I know we should use stored procedures for database access (everyone in this newsgroup says that is the only way for database access) but that isn't feasible since it is an uphill battle -- another story. From experience, I know SQL Server can work with only using ad-hoc statements and it is fast. For database access, we started with DAO, then RDO, then ADO, and now ADO.NET. We also started with ODBC but now use OLE DB connections. During that entire time, we never noticed any performance degradation using SQL Server instead of Access. SQL Server was always faster and more reliable. I haven't used MFC so I can't comment on data access in that environment but I would bet the problem is not SQL Server but rather your connection to it (or how you are querying the data). I don't know what "exec sp_prepexec" stored procedure is but if it is a user stored procedure, you should rename it to NOT use sp_XXX or spXXX because there are performance implications with that. (In our company we use pg_spXXXX.) I can't answer your specific question but I don't believe that SQL Server is 100 times slower. It should be the exact opposite! Good luck! Let us know if you figure it out. Steve Mark Baker said the following on 7/8/2005 2:25 AM: Show quote > Yikes. > Thanks for the honest answer. That's probably the worst news I could > have had today! I guess it means that SQLServer is inherently slower > than Access for client recordset operations. > > If this is the case (and I can see the logic) why do Microsoft provide > an upsizing wizard at all? > > Thanks > Mark > > > > *** Sent via Developersdex http://www.developersdex.com *** Hi
sp_prepexec is called by SqlCommand.Prepare, it is not a user created procedure and you should not rename it. If you are going to call the same query multiple times then you will save substantial time by re-using the prepared SQLCommand. Look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/ScaleNetChapt12.asp on ways to improve your ADO.Net applications. John Show quote "Steve Beach" wrote: > Mark, > > In my software company, we initially used Access as the backend > database. Eventually we upsized to SQL Server but supported both > versions for a while (until we realized that Access was evil and dumped > it! Corruption, etc. Probably the same reasons why everyones leave it.) > > Before we upsized, we used only ad-hoc queries (what else is there?) and > continue to only use them today. I know we should use stored procedures > for database access (everyone in this newsgroup says that is the only > way for database access) but that isn't feasible since it is an uphill > battle -- another story. From experience, I know SQL Server can work > with only using ad-hoc statements and it is fast. > > For database access, we started with DAO, then RDO, then ADO, and now > ADO.NET. We also started with ODBC but now use OLE DB connections. > During that entire time, we never noticed any performance degradation > using SQL Server instead of Access. SQL Server was always faster and > more reliable. > > I haven't used MFC so I can't comment on data access in that environment > but I would bet the problem is not SQL Server but rather your connection > to it (or how you are querying the data). > > I don't know what "exec sp_prepexec" stored procedure is but if it is a > user stored procedure, you should rename it to NOT use sp_XXX or spXXX > because there are performance implications with that. (In our company > we use pg_spXXXX.) > > I can't answer your specific question but I don't believe that SQL > Server is 100 times slower. It should be the exact opposite! > > Good luck! Let us know if you figure it out. > > Steve > > > Mark Baker said the following on 7/8/2005 2:25 AM: > > Yikes. > > Thanks for the honest answer. That's probably the worst news I could > > have had today! I guess it means that SQLServer is inherently slower > > than Access for client recordset operations. > > > > If this is the case (and I can see the logic) why do Microsoft provide > > an upsizing wizard at all? > > > > Thanks > > Mark > > > > > > > > *** Sent via Developersdex http://www.developersdex.com *** >  |
|||||||||||||||||||||||
Other interesting topics