|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why run time double in a script?I have table value UDFs. It takes about 2.5 minutes when I execute
select * from dbo.myudf('...') I have a stored procedure to run thousands of the UDFs as dynaml SQL: @sql = 'insert into @tablevalue select '+.....+' from dbo.myudf('+...+'...)' exec (@sql) However, it takes about 4.7 minutes to run the same query. Does the dynamal sql cause the slowness? Or how much will checkpoint help? I already set the database as simple Recovery model.
Show quote
>I have table value UDFs. It takes about 2.5 minutes when I execute Well, you're writing the data into another table, which is not the same as > select * from dbo.myudf('...') > > I have a stored procedure to run thousands of the UDFs as dynaml SQL: > > @sql = 'insert into @tablevalue select '+.....+' from > dbo.myudf('+...+'...)' > exec (@sql) > > However, it takes about 4.7 minutes to run the same query. Does the > dynamal > sql cause the slowness? Or how much will checkpoint help? I already set > the > database as simple Recovery model. just select *. As much as you might want to believe otherwise, it is not necessarily the case that your table variable is created solely in memory. And even if it were, it is not necessarily the case that you get the table population 'for free'... e.g. it will consume resources to do that. The data written in table variable is in fact not big. About 200K rows at
most with only two float columns and one varchar(100) column.... Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > >I have table value UDFs. It takes about 2.5 minutes when I execute > > select * from dbo.myudf('...') > > > > I have a stored procedure to run thousands of the UDFs as dynaml SQL: > > > > @sql = 'insert into @tablevalue select '+.....+' from > > dbo.myudf('+...+'...)' > > exec (@sql) > > > > However, it takes about 4.7 minutes to run the same query. Does the > > dynamal > > sql cause the slowness? Or how much will checkpoint help? I already set > > the > > database as simple Recovery model. > > Well, you're writing the data into another table, which is not the same as > just select *. > > As much as you might want to believe otherwise, it is not necessarily the > case that your table variable is created solely in memory. And even if it > were, it is not necessarily the case that you get the table population 'for > free'... e.g. it will consume resources to do that. > > > > The data written in table variable is in fact not big. While size matters, it is not everything. As Alexander suggested, you are having a hard time diagnosng the problem because you changed two things at once. (1) you turned a static query into dynamic SQL, and (2) you added population of a table variable. This makes it very difficult to determine what change introduced the slowness. Make one change at a time... A It's still costlier to INSERT data into a table than to SELECT data from a
table. SQL has to ensure the ATOMicity of INSERTs, log them, update any indexes, possibly re-arrange the table if you have a clustered index, maintain locks, etc., etc., etc. SELECT doesn't have as much overhead. Show quote "nick" <n***@discussions.microsoft.com> wrote in message news:E589C286-4C4D-4F62-9E28-DFB269707B56@microsoft.com... > The data written in table variable is in fact not big. About 200K rows at > most with only two float columns and one varchar(100) column.... > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> >I have table value UDFs. It takes about 2.5 minutes when I execute >> > select * from dbo.myudf('...') >> > >> > I have a stored procedure to run thousands of the UDFs as dynaml SQL: >> > >> > @sql = 'insert into @tablevalue select '+.....+' from >> > dbo.myudf('+...+'...)' >> > exec (@sql) >> > >> > However, it takes about 4.7 minutes to run the same query. Does the >> > dynamal >> > sql cause the slowness? Or how much will checkpoint help? I already set >> > the >> > database as simple Recovery model. >> >> Well, you're writing the data into another table, which is not the same >> as >> just select *. >> >> As much as you might want to believe otherwise, it is not necessarily the >> case that your table variable is created solely in memory. And even if >> it >> were, it is not necessarily the case that you get the table population >> 'for >> free'... e.g. it will consume resources to do that. >> >> >> nick wrote:
> I have table value UDFs. It takes about 2.5 minutes when I execute you probably need to separate the UDF execution time and the time to> select * from dbo.myudf('...') > > I have a stored procedure to run thousands of the UDFs as dynaml SQL: > > @sql = 'insert into @tablevalue select '+.....+' from dbo.myudf('+...+'...)' > exec (@sql) > > However, it takes about 4.7 minutes to run the same query. Does the dynamal > sql cause the slowness? Or how much will checkpoint help? I already set the > database as simple Recovery model. insert into a table variable. I would suggest that you materialize the output of the UDF into a staging table, populate the table variable from the staging table and see how much time that would take. I rechecked the rows returned by the UDF It's actually only 20 rows.
Show quote "Alexander Kuznetsov" wrote: > > nick wrote: > > I have table value UDFs. It takes about 2.5 minutes when I execute > > select * from dbo.myudf('...') > > > > I have a stored procedure to run thousands of the UDFs as dynaml SQL: > > > > @sql = 'insert into @tablevalue select '+.....+' from dbo.myudf('+...+'...)' > > exec (@sql) > > > > However, it takes about 4.7 minutes to run the same query. Does the dynamal > > sql cause the slowness? Or how much will checkpoint help? I already set the > > database as simple Recovery model. > > you probably need to separate the UDF execution time and the time to > insert into a table variable. I would suggest that you materialize the > output of the UDF into a staging table, populate the table variable > from the staging table and see how much time that would take. > > >I rechecked the rows returned by the UDF It's actually only 20 rows. Wow, 20 rows takes over two minutes?If I were in your shoes (and yes, I know I'm not), I think I'd focus on the model so that the query itself doesn't take so long, instead of worrying about how to speed it up when I throw a table variable and dynamic SQL on top of it (both things which will *at best* keep the query time the same, but more often than not, increase it, in most scenarios). A nick (n***@discussions.microsoft.com) writes:
> I have table value UDFs. It takes about 2.5 minutes when I execute I don't really see the point of inserting into a table variable in> select * from dbo.myudf('...') > > I have a stored procedure to run thousands of the UDFs as dynaml SQL: > > @sql = 'insert into @tablevalue select '+.....+' from > dbo.myudf('+...+'...)' > exec (@sql) > > However, it takes about 4.7 minutes to run the same query. Does the > dynamal sql cause the slowness? Or how much will checkpoint help? I > already set the database as simple Recovery model. dynamic SQL... As the other folks, I would more suspect the INSERT than the dynamic SQL. When you run the function from the INSERT statement, it will execute in the context of a transaction. Depending on the isolation level, this could affect the execution time, particularly if there is blocking. You should alo run this several times to make sure. The difference could be due to natural variation because of the load on the server. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Based on nick's most recent admission, I'm suspecting a poorly written query
or lack of indexes inside the UDF. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns97EC413B8EC3Yazorman@127.0.0.1... > nick (n***@discussions.microsoft.com) writes: >> I have table value UDFs. It takes about 2.5 minutes when I execute >> select * from dbo.myudf('...') >> >> I have a stored procedure to run thousands of the UDFs as dynaml SQL: >> >> @sql = 'insert into @tablevalue select '+.....+' from >> dbo.myudf('+...+'...)' >> exec (@sql) >> >> However, it takes about 4.7 minutes to run the same query. Does the >> dynamal sql cause the slowness? Or how much will checkpoint help? I >> already set the database as simple Recovery model. > > I don't really see the point of inserting into a table variable in > dynamic SQL... > > As the other folks, I would more suspect the INSERT than the dynamic > SQL. When you run the function from the INSERT statement, it will > execute in the context of a transaction. Depending on the isolation > level, this could affect the execution time, particularly if there > is blocking. > > You should alo run this several times to make sure. The difference > could be due to natural variation because of the load on the server. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Ok, let me explain why dynamical SQL and let you know if the query is poor
written. The UDFs has one or two parameter and return a 20 some rows. The UDF will calculate the meen value group by a category column. Since SQL Server don't have aggregete function Mean(), I had to insert to data into a temp/table variable. I tried write a stored procedure with temp table and create index. it has the same performance as the UDF i wrote with table variable (Because UDF doesn't allow temp table). In fact, I defined primary key in the table varable for index to achieve the same performance. Unless using SQL 2005 with .Net aggregate function, I don't see a better solution for mean function. Why dynamical SQL? there are thousands of the UDFs, and a simple joinning with the result of the UDF with other tables to get the final result. I know it's hard since I didn't post all the code. But the UDFs should have constant speed. And the dynamical SQL doesn't take much time when trying a single step. My question why a single step takes only half the time? comparing with the same step in a loop of thousands. Show quote "Mike C#" wrote: > Based on nick's most recent admission, I'm suspecting a poorly written query > or lack of indexes inside the UDF. > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns97EC413B8EC3Yazorman@127.0.0.1... > > nick (n***@discussions.microsoft.com) writes: > >> I have table value UDFs. It takes about 2.5 minutes when I execute > >> select * from dbo.myudf('...') > >> > >> I have a stored procedure to run thousands of the UDFs as dynaml SQL: > >> > >> @sql = 'insert into @tablevalue select '+.....+' from > >> dbo.myudf('+...+'...)' > >> exec (@sql) > >> > >> However, it takes about 4.7 minutes to run the same query. Does the > >> dynamal sql cause the slowness? Or how much will checkpoint help? I > >> already set the database as simple Recovery model. > > > > I don't really see the point of inserting into a table variable in > > dynamic SQL... > > > > As the other folks, I would more suspect the INSERT than the dynamic > > SQL. When you run the function from the INSERT statement, it will > > execute in the context of a transaction. Depending on the isolation > > level, this could affect the execution time, particularly if there > > is blocking. > > > > You should alo run this several times to make sure. The difference > > could be due to natural variation because of the load on the server. > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Books Online for SQL Server 2005 at > > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > > Books Online for SQL Server 2000 at > > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > In fact, I should never mention the dynamical and UDF part. It sounds not
relevent to the problem. nick wrote:
Show quote >Ok, let me explain why dynamical SQL and let you know if the query is poor The SQL aggregate function AVG() calculates the mean. Just be sure to >written. > >The UDFs has one or two parameter and return a 20 some rows. The UDF will >calculate the meen value group by a category column. Since SQL Server don't >have aggregete function Mean(), I had to insert to data into a temp/table >variable. I tried write a stored procedure with temp table and create index. >it has the same performance as the UDF i wrote with table variable (Because >UDF doesn't allow temp table). In fact, I defined primary key in the table >varable for index to achieve the same performance. > >Unless using SQL 2005 with .Net aggregate function, I don't see a better >solution for mean function. > > coerce integer values to float or decimal, if you want a non-integer mean of integer data. select AVG(1.0*x) from ( select 1 as x union all select 2 as x union all select 5 as x ) T Steve Kass Drew University Show quote >Why dynamical SQL? there are thousands of the UDFs, and a simple joinning >with the result of the UDF with other tables to get the final result. > >I know it's hard since I didn't post all the code. But the UDFs should have >constant speed. And the dynamical SQL doesn't take much time when trying a >single step. > >My question why a single step takes only half the time? comparing with the >same step in a loop of thousands. > > > >"Mike C#" wrote: > > > >>Based on nick's most recent admission, I'm suspecting a poorly written query >>or lack of indexes inside the UDF. >> >>"Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>news:Xns97EC413B8EC3Yazorman@127.0.0.1... >> >> >>>nick (n***@discussions.microsoft.com) writes: >>> >>> >>>>I have table value UDFs. It takes about 2.5 minutes when I execute >>>>select * from dbo.myudf('...') >>>> >>>>I have a stored procedure to run thousands of the UDFs as dynaml SQL: >>>> >>>>@sql = 'insert into @tablevalue select '+.....+' from >>>>dbo.myudf('+...+'...)' >>>>exec (@sql) >>>> >>>>However, it takes about 4.7 minutes to run the same query. Does the >>>>dynamal sql cause the slowness? Or how much will checkpoint help? I >>>>already set the database as simple Recovery model. >>>> >>>> >>>I don't really see the point of inserting into a table variable in >>>dynamic SQL... >>> >>>As the other folks, I would more suspect the INSERT than the dynamic >>>SQL. When you run the function from the INSERT statement, it will >>>execute in the context of a transaction. Depending on the isolation >>>level, this could affect the execution time, particularly if there >>>is blocking. >>> >>>You should alo run this several times to make sure. The difference >>>could be due to natural variation because of the load on the server. >>> >>>-- >>>Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>>Books Online for SQL Server 2005 at >>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>Books Online for SQL Server 2000 at >>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >>> >> >> >> mean sometimes means average. What I've implemented is median....
Show quote "Steve Kass" wrote: > > > nick wrote: > > >Ok, let me explain why dynamical SQL and let you know if the query is poor > >written. > > > >The UDFs has one or two parameter and return a 20 some rows. The UDF will > >calculate the meen value group by a category column. Since SQL Server don't > >have aggregete function Mean(), I had to insert to data into a temp/table > >variable. I tried write a stored procedure with temp table and create index. > >it has the same performance as the UDF i wrote with table variable (Because > >UDF doesn't allow temp table). In fact, I defined primary key in the table > >varable for index to achieve the same performance. > > > >Unless using SQL 2005 with .Net aggregate function, I don't see a better > >solution for mean function. > > > > > > The SQL aggregate function AVG() calculates the mean. Just be sure to > coerce > integer values to float or decimal, if you want a non-integer mean of > integer data. > > select AVG(1.0*x) > from ( > select 1 as x > union all > select 2 as x > union all > select 5 as x > ) T > > Steve Kass > Drew University > > >Why dynamical SQL? there are thousands of the UDFs, and a simple joinning > >with the result of the UDF with other tables to get the final result. > > > >I know it's hard since I didn't post all the code. But the UDFs should have > >constant speed. And the dynamical SQL doesn't take much time when trying a > >single step. > > > >My question why a single step takes only half the time? comparing with the > >same step in a loop of thousands. > > > > > > > >"Mike C#" wrote: > > > > > > > >>Based on nick's most recent admission, I'm suspecting a poorly written query > >>or lack of indexes inside the UDF. > >> > >>"Erland Sommarskog" <esq***@sommarskog.se> wrote in message > >>news:Xns97EC413B8EC3Yazorman@127.0.0.1... > >> > >> > >>>nick (n***@discussions.microsoft.com) writes: > >>> > >>> > >>>>I have table value UDFs. It takes about 2.5 minutes when I execute > >>>>select * from dbo.myudf('...') > >>>> > >>>>I have a stored procedure to run thousands of the UDFs as dynaml SQL: > >>>> > >>>>@sql = 'insert into @tablevalue select '+.....+' from > >>>>dbo.myudf('+...+'...)' > >>>>exec (@sql) > >>>> > >>>>However, it takes about 4.7 minutes to run the same query. Does the > >>>>dynamal sql cause the slowness? Or how much will checkpoint help? I > >>>>already set the database as simple Recovery model. > >>>> > >>>> > >>>I don't really see the point of inserting into a table variable in > >>>dynamic SQL... > >>> > >>>As the other folks, I would more suspect the INSERT than the dynamic > >>>SQL. When you run the function from the INSERT statement, it will > >>>execute in the context of a transaction. Depending on the isolation > >>>level, this could affect the execution time, particularly if there > >>>is blocking. > >>> > >>>You should alo run this several times to make sure. The difference > >>>could be due to natural variation because of the load on the server. > >>> > >>>-- > >>>Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >>> > >>>Books Online for SQL Server 2005 at > >>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >>>Books Online for SQL Server 2000 at > >>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >>> > >>> > >> > >> > >> > "nick" <n***@discussions.microsoft.com> wrote in message Ahhh, mean is not the same as median, except that the median in a set with news:CBDAABBF-F66B-4B05-80A5-30180AE5DC1E@microsoft.com... > mean sometimes means average. What I've implemented is median.... an even number of members is the mean of the middle two. I have no idea what your code looks like since you didn't supply any DDL, DML or anything else that can be used to help troubleshoot your problem, but to try and answer your question: The SQL Server engine is set-based. It's highly tuned to perform set-based operations on large quantities of data in one shot; procedural code very often suffers on SQL Server because of the emphasis on set-based operations. I have no idea what you're putting in your temp table, but here's a simple example of grabbing the median of a column: CREATE TABLE #Test (Price NUMERIC(10, 2) NOT NULL PRIMARY KEY) INSERT INTO #Test (Price) SELECT 10.20 UNION SELECT 11.40 UNION SELECT 4.30 UNION SELECT 12.50 UNION SELECT 8.30 SELECT SUM(c.Price) / 2.0 AS Median FROM ( SELECT MAX(a.Price) AS Price FROM ( SELECT TOP 50 PERCENT Price FROM #Test ORDER BY Price ASC ) a UNION ALL SELECT MIN(b.Price) FROM ( SELECT TOP 50 PERCENT Price FROM #Test ORDER BY Price DESC ) b ) c DROP TABLE #Test This is good to calculate one median in a data set. It's difficult to use it
to calculate multiple medians grouping by some columns.... Show quote "Mike C#" wrote: > > "nick" <n***@discussions.microsoft.com> wrote in message > news:CBDAABBF-F66B-4B05-80A5-30180AE5DC1E@microsoft.com... > > mean sometimes means average. What I've implemented is median.... > > Ahhh, mean is not the same as median, except that the median in a set with > an even number of members is the mean of the middle two. I have no idea > what your code looks like since you didn't supply any DDL, DML or anything > else that can be used to help troubleshoot your problem, but to try and > answer your question: The SQL Server engine is set-based. It's highly > tuned to perform set-based operations on large quantities of data in one > shot; procedural code very often suffers on SQL Server because of the > emphasis on set-based operations. I have no idea what you're putting in > your temp table, but here's a simple example of grabbing the median of a > column: > > CREATE TABLE #Test (Price NUMERIC(10, 2) NOT NULL PRIMARY KEY) > > INSERT INTO #Test (Price) > SELECT 10.20 > UNION SELECT 11.40 > UNION SELECT 4.30 > UNION SELECT 12.50 > UNION SELECT 8.30 > > SELECT SUM(c.Price) / 2.0 AS Median > FROM ( > SELECT MAX(a.Price) AS Price > FROM > ( > SELECT TOP 50 PERCENT Price > FROM #Test > ORDER BY Price ASC > ) a > UNION ALL > SELECT MIN(b.Price) > FROM > ( > SELECT TOP 50 PERCENT Price > FROM #Test > ORDER BY Price DESC > ) b > ) c > > DROP TABLE #Test > > > > "nick" <n***@discussions.microsoft.com> wrote in message Sorry to hear that doesn't work out for you. Unfortunately I'm not going to news:96026AC0-A18E-4A25-8403-20C56EE1AD86@microsoft.com... > This is good to calculate one median in a data set. It's difficult to use > it > to calculate multiple medians grouping by some columns.... keep guessing at fixes when you won't even explain the problem, man. What you posted right above this sounds like a very simple change to what I previously posted, but unfortunately I don't have the time or patience to keep blindly guessing at exactly what you're doing. Peace Nick,
Here is an efficient way to find a median if you are using SQL Server 2005. This query gives the median [Freight] for each customer's orders: select CustomerID, avg(Freight) as medianFreight from ( select CustomerID, Freight, row_number() over (partition by CustomerID order by Freight asc, OrderID asc) as r1, row_number() over (partition by CustomerID order by Freight desc, OrderID desc) as r2 from Northwind..Orders ) T where r1 - r2 between -1 and 1 group by CustomerID -- Idea from Itzik Ben-Gan's book T-SQL Querying -- SK nick wrote: Show quote >mean sometimes means average. What I've implemented is median.... > >"Steve Kass" wrote: > > > >>nick wrote: >> >> >> >>>Ok, let me explain why dynamical SQL and let you know if the query is poor >>>written. >>> >>>The UDFs has one or two parameter and return a 20 some rows. The UDF will >>>calculate the meen value group by a category column. Since SQL Server don't >>>have aggregete function Mean(), I had to insert to data into a temp/table >>>variable. I tried write a stored procedure with temp table and create index. >>>it has the same performance as the UDF i wrote with table variable (Because >>>UDF doesn't allow temp table). In fact, I defined primary key in the table >>>varable for index to achieve the same performance. >>> >>>Unless using SQL 2005 with .Net aggregate function, I don't see a better >>>solution for mean function. >>> >>> >>> >>> >>The SQL aggregate function AVG() calculates the mean. Just be sure to >>coerce >>integer values to float or decimal, if you want a non-integer mean of >>integer data. >> >>select AVG(1.0*x) >>from ( >> select 1 as x >> union all >> select 2 as x >> union all >> select 5 as x >>) T >> >>Steve Kass >>Drew University >> >> >> >>>Why dynamical SQL? there are thousands of the UDFs, and a simple joinning >>>with the result of the UDF with other tables to get the final result. >>> >>>I know it's hard since I didn't post all the code. But the UDFs should have >>>constant speed. And the dynamical SQL doesn't take much time when trying a >>>single step. >>> >>>My question why a single step takes only half the time? comparing with the >>>same step in a loop of thousands. >>> >>> >>> >>>"Mike C#" wrote: >>> >>> >>> >>> >>> >>>>Based on nick's most recent admission, I'm suspecting a poorly written query >>>>or lack of indexes inside the UDF. >>>> >>>>"Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>>news:Xns97EC413B8EC3Yazorman@127.0.0.1... >>>> >>>> >>>> >>>> >>>>>nick (n***@discussions.microsoft.com) writes: >>>>> >>>>> >>>>> >>>>> >>>>>>I have table value UDFs. It takes about 2.5 minutes when I execute >>>>>>select * from dbo.myudf('...') >>>>>> >>>>>>I have a stored procedure to run thousands of the UDFs as dynaml SQL: >>>>>> >>>>>>@sql = 'insert into @tablevalue select '+.....+' from >>>>>>dbo.myudf('+...+'...)' >>>>>>exec (@sql) >>>>>> >>>>>>However, it takes about 4.7 minutes to run the same query. Does the >>>>>>dynamal sql cause the slowness? Or how much will checkpoint help? I >>>>>>already set the database as simple Recovery model. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>I don't really see the point of inserting into a table variable in >>>>>dynamic SQL... >>>>> >>>>>As the other folks, I would more suspect the INSERT than the dynamic >>>>>SQL. When you run the function from the INSERT statement, it will >>>>>execute in the context of a transaction. Depending on the isolation >>>>>level, this could affect the execution time, particularly if there >>>>>is blocking. >>>>> >>>>>You should alo run this several times to make sure. The difference >>>>>could be due to natural variation because of the load on the server. >>>>> >>>>>-- >>>>>Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>> >>>>>Books Online for SQL Server 2005 at >>>>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>>>Books Online for SQL Server 2000 at >>>>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>>> Probably UDA is better in SQL 2005? However I am using V2000
Show quote "Steve Kass" wrote: > Nick, > > Here is an efficient way to find a median if you are using SQL Server 2005. > This query gives the median [Freight] for each customer's orders: > > select > CustomerID, > avg(Freight) as medianFreight > from ( > select > CustomerID, > Freight, > row_number() over (partition by CustomerID order by Freight asc, > OrderID asc) as r1, > row_number() over (partition by CustomerID order by Freight desc, > OrderID desc) as r2 > from Northwind..Orders > ) T > where r1 - r2 between -1 and 1 > group by CustomerID > -- Idea from Itzik Ben-Gan's book T-SQL Querying > > -- SK > > nick wrote: > > >mean sometimes means average. What I've implemented is median.... > > > >"Steve Kass" wrote: > > > > > > > >>nick wrote: > >> > >> > >> > >>>Ok, let me explain why dynamical SQL and let you know if the query is poor > >>>written. > >>> > >>>The UDFs has one or two parameter and return a 20 some rows. The UDF will > >>>calculate the meen value group by a category column. Since SQL Server don't > >>>have aggregete function Mean(), I had to insert to data into a temp/table > >>>variable. I tried write a stored procedure with temp table and create index. > >>>it has the same performance as the UDF i wrote with table variable (Because > >>>UDF doesn't allow temp table). In fact, I defined primary key in the table > >>>varable for index to achieve the same performance. > >>> > >>>Unless using SQL 2005 with .Net aggregate function, I don't see a better > >>>solution for mean function. > >>> > >>> > >>> > >>> > >>The SQL aggregate function AVG() calculates the mean. Just be sure to > >>coerce > >>integer values to float or decimal, if you want a non-integer mean of > >>integer data. > >> > >>select AVG(1.0*x) > >>from ( > >> select 1 as x > >> union all > >> select 2 as x > >> union all > >> select 5 as x > >>) T > >> > >>Steve Kass > >>Drew University > >> > >> > >> > >>>Why dynamical SQL? there are thousands of the UDFs, and a simple joinning > >>>with the result of the UDF with other tables to get the final result. > >>> > >>>I know it's hard since I didn't post all the code. But the UDFs should have > >>>constant speed. And the dynamical SQL doesn't take much time when trying a > >>>single step. > >>> > >>>My question why a single step takes only half the time? comparing with the > >>>same step in a loop of thousands. > >>> > >>> > >>> > >>>"Mike C#" wrote: > >>> > >>> > >>> > >>> > >>> > >>>>Based on nick's most recent admission, I'm suspecting a poorly written query > >>>>or lack of indexes inside the UDF. > >>>> > >>>>"Erland Sommarskog" <esq***@sommarskog.se> wrote in message > >>>>news:Xns97EC413B8EC3Yazorman@127.0.0.1... > >>>> > >>>> > >>>> > >>>> > >>>>>nick (n***@discussions.microsoft.com) writes: > >>>>> > >>>>> > >>>>> > >>>>> > >>>>>>I have table value UDFs. It takes about 2.5 minutes when I execute > >>>>>>select * from dbo.myudf('...') > >>>>>> > >>>>>>I have a stored procedure to run thousands of the UDFs as dynaml SQL: > >>>>>> > >>>>>>@sql = 'insert into @tablevalue select '+.....+' from > >>>>>>dbo.myudf('+...+'...)' > >>>>>>exec (@sql) > >>>>>> > >>>>>>However, it takes about 4.7 minutes to run the same query. Does the > >>>>>>dynamal sql cause the slowness? Or how much will checkpoint help? I > >>>>>>already set the database as simple Recovery model. > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>I don't really see the point of inserting into a table variable in > >>>>>dynamic SQL... > >>>>> > >>>>>As the other folks, I would more suspect the INSERT than the dynamic > >>>>>SQL. When you run the function from the INSERT statement, it will > >>>>>execute in the context of a transaction. Depending on the isolation > >>>>>level, this could affect the execution time, particularly if there > >>>>>is blocking. > >>>>> > >>>>>You should alo run this several times to make sure. The difference > >>>>>could be due to natural variation because of the load on the server. > >>>>> > >>>>>-- > >>>>>Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > >>>>> > >>>>>Books Online for SQL Server 2005 at > >>>>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > >>>>>Books Online for SQL Server 2000 at > >>>>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >>>>> > >>>>> > >>>>> > >>>>> > >>>> > >>>> > >>>> > >>>> >  |
|||||||||||||||||||||||
Other interesting topics