Eric Barro
ebarro at afsweb.com
Tue Nov 9 08:51:18 CST 2004
Michael, I really didn't see any performance problems when I ran the stored procedure so I was baffled why our dba was not very keen on having me implement the solution I found. I also read somewhere that SQL server (at least from ver 7.0 and above) uses a stored procedure called sp_executesql that uses the same execution plan for similar queries and since each SELECT statement for each dynamic column would be about the same (the only change would be the column name), I believe that SQL server was in fact doing exactly just that. Eric -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Michael Maddison Sent: Tuesday, November 09, 2004 6:09 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] leveraging the power of the database versus code Hi guys, I don't think there are any really good ways of pivoting in SQL 2K... I have a db that requires some reports based on pivot tables, but I'm lucky in that the data is only updated each month so I schedule a sproc that updates a scratch table with the pivoted data, it takes quite a while but once its done the reports run like a dream. We use cursors and a loop. Another option is to use OLAP. You state that using dynamic sql means that the sproc is not precompiled... I think this is wrong! Its probably using the 1st sql statement run since last startups plan. Which may explain your performance problems.. or not :-) Try adding WITH RECOMPILE to the sproc. This will force SQL to create a new plan each execution. Also check out parameter sniffing which may be hurting you as well. I presume your indexes are ok and the plan in QA shows only seeks not scans? cheers Michael M Hello Eric, I am interested in this topic also. I recently had to create a SQL statement that selected the top n number of records. It was a reasonably complex sql statement filtering on a table with 12 million records. In SQL you can say Select top 10 .... but you cannot say Select top @MyValue .... So, the only way I could think of doing this was by concatenating a sql string together and then executing it. Of course this is similar to what you are doing and the downside is that SQL can never precompile the sql because it is effectively dynamic. Performance did drop, and in the end, I had to drop ,my order by clause which gained me a little increase in speed, however, I would have preferred not to drop it. I have no suggestions for myself, and I am interested in whether you get any insights for your very similar problem. Thanks Eric, Mark Breen Ireland On Mon, 8 Nov 2004 20:44:33 -0800, Eric Barro <ebarro at afsweb.com> wrote: > Ok I need to generate a crosstab query (aka pivot table) using SQL server. The problem with SQL server is that it can't handle dynamically generated columns. In other words you would need to hard code the names of the columns which assumes that you know them beforehand. However, in most cases, the column values could not be determined ahead of time. Access of course handles this with great aplomb. But SQL server? Well...that's another story. :) > > So...I found a way to do this utilizing a stored procedure that dynamically generates the SELECT statements and uses a temporary table to store the values. You'd think that would be the end of the story...BUT the dba balked at the proposed method since according to him SQL server was not designed to handle this sort of stuff and that the dynamically generated SELECT statements would be highly inefficient. > > My question then to the group is this... > > Is it not better to let SQL server handle the load and the processing on the server side rather than let the client side handle all that processing? > > To take an example: > > State Product1 Product2 Product3 > CA 1000 500 200 > MA 900 200 450 > NV 600 600 600 > > where Products are dynamically generated > > The method I discovered to produce the desired results using a stored procedure worked. The suggestion I got was to handle all of that processing in code (outside of SQL server). Which means that in order to produce the desired results above, the code would need to grab the recordset from SQL server, and for each column generate the appropriate SELECT statement to return the values to either populate an in-memory array or post the results to a table from which it can read the final results. > > Now...given that scenario isn't it more efficient to let SQL server handle all of that processing given that it handles all of the processing on the server side instead of client side basically resulting in less round trips from the web server to the SQL server? > > I would appreciate feedback from you guys. The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is contrary to company policy. The company will not accept any liability in respect of such communication.