Re: SQL DBA Consultants
From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 11/23/04
- Next message: Mark: "Saving Trace Template"
- Previous message: Jordan: "IF THEN question"
- In reply to: David Mroz: "Re: SQL DBA Consultants"
- Next in thread: David Mroz: "Re: SQL DBA Consultants"
- Reply: David Mroz: "Re: SQL DBA Consultants"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 23 Nov 2004 11:40:34 -0600
Thanks. Also, you mentioned (in a reply to Peter) that you emailed him.
Don't try to email me. The email addy that I am displaying within the
newsgroups is an old one. Even if you unmunged the address it would bounce
back as unavailable.
-- Keith "David Mroz" <dave@glimmernet.com> wrote in message news:%23a1ZvLY0EHA.3588@TK2MSFTNGP14.phx.gbl... > Oh yeah - this will give you an aneurism. Luckily, the number of weights is > fixed otherwise this would be a super huge problem. > > We're not using cursoring or anything. When I mentioned "loops" it's just > because that's the way it appears if you trace the execution. It's all done > in subqueries. > > Yes, table name is the datafile and directly relates to the FROM clause. Q1 > and Q2 are column names. They get passed in as parameters and then a huge > (really huge) SQL statement is constructed. This is one of the issues that > I am trying to resolve as it is over the 8000 character limit and will not > execute once inside of the SP. This code only works in the QA due to it's > length. > > I'll generate the code you asked for and get back to you in a few minutes. > > Thanks > > Dave > > "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message > news:O7jL$zX0EHA.3836@TK2MSFTNGP11.phx.gbl... > > Ouch. My head hurts already. I see lots of duplication. The trick will > > be > > figuring out how to re-write this beast so that it performs well and it is > > easy to maintain. Based on all the duplication I think that it might be > > possible as long as we can figure out the process and understand the > > expected results. > > > > You say that this is part of a stored procedure and you pass in TableName, > > Q1 and Q2 as variables. By TableName I assume that you refer to DataFile. > > I don't know how/where Q1 and Q2 come into play. If you are using > > dynamic > > sql within a stored procedure I am guessing that you are building and then > > executing a (huge) sql string. Is that correct? > > > > You mention that this is part of a loop. So the stored procedure loops > > (cursors) through data in order to generate the results? Looping > > (cursoring) is generally bad for performance. If it can be avoided > > performance almost always improves. > > > > I assume that you have one formula to create the standard error. Knowing > > what this formula is can't hurt; it might even help. > > > > Can you provide a CREATE TABLE script for DataFile? > > Can you also provide enough sample rows (in the form of INSERT INTO > > statements) that I (or anyone else reading this post) can use to test our > > efforts? > > Finally, please provide the expected results. > > > > ....something like this: > > create table #foo (col1 int, col2 money, col3 datetime) > > insert into #foo (col1, col2, col3) values (1,10.50,getdate()) > > insert into #foo (col1, col2, col3) values (4,25,'20041122') > > insert into #foo (col1, col2, col3) values (5, 30.75, '20041121') > > insert into #foo (col1, col2, col3) values (20,60,'20041001') > > > > SELECT AVG(col1) AS Col1Avg, SUM(Col1) AS Col1Sum, AVG(col2) AS Col2Avg, > > SUM(Col2) AS Col2Sum, SQRT(SUM(Col2)) AS Col2Sqrt, MIN(col3) AS FirstDate, > > MAX(col3) AS MaxDate > > FROM #foo > > > > expected result: > > Col1Avg Col1Sum Col2Avg Col2Sum > > Col2Sqrt > > FirstDate MaxDate > > > > ----------- ----------- --------------------- --------------------- ------ -- > > --------------------------------------------- ---------------------------- -- > > ------------------------ ------------------------------------------------- -- > > --- > > 7 30 31.5625 126.2500 > > 11.236102527122116 2004-10-01 > > 00:00:00.000 2004-11-23 10:47:18.113 > > > > > > > > -- > > Keith > > > > > > "David Mroz" <dave@glimmernet.com> wrote in message > > news:uwjgakX0EHA.1408@TK2MSFTNGP10.phx.gbl... > >> Ok - here we go. This is cleaned up a little, but for the most part, > >> it's > >> the exact code. All of it. It's long. All it does is calculate the > >> percentage and standard error associated with a given set of variables in > > a > >> table name. TableName, Q1 and Q2 are all variables passed to the SP. > >> > >> It's all a loop that calculates the Standard Error based on a formula > > which > >> I can provide if necessary. It has to take into account 50 weights which > >> are factored into the formula as AWTn. > >> > >> The issue is the length and the execution time. Assuming SQL Server > > doesn't > >> optimize the code, it executes the same calculations 50 times per record > > in > >> the recordset. This SP works, however it runs slow, so I'd like to > > optimize > >> the SP and reduce the amount of code it takes to get this so that I can > > make > >> the SP more dynamic. > >> > >> Thanks > >> Dave > > <snip> > >> > >> > >> > >> > >> "Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message > >> news:Od99WjW0EHA.1260@TK2MSFTNGP12.phx.gbl... > >> > Post your specific question here. You will probably receive lots of > > help. > >> > It sounds like your stored procedure is fairly complex so it would be > >> > helpful to have the stored procedure code, CREATE TABLE script(s), and > >> > INSERT INTO scripts that load sample data into the table(s). We don't > >> > need > >> > lots of sample data -- only enough to be able to work through the > >> > stored > >> > procedure. > >> > > >> > -- > >> > Keith > >> > > >> > > >> > > > >
- Next message: Mark: "Saving Trace Template"
- Previous message: Jordan: "IF THEN question"
- In reply to: David Mroz: "Re: SQL DBA Consultants"
- Next in thread: David Mroz: "Re: SQL DBA Consultants"
- Reply: David Mroz: "Re: SQL DBA Consultants"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
