Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Even
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).
No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA
|||As I was reviewing my SPs, I realized that and implemented something similar.
Speed increase when I did that was minimal but any speed increase is welcomed
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> to
> take
> SELECT
> it's
> major
> Even
> since
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
Monday, March 26, 2012
Question about optimization
Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Eve
n
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar
.
Speed increase when I did that was minimal but any speed increase is welcome
d
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries
.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far
.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> to
> take
> SELECT
> it's
> major
> Even
> since
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>sql
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Eve
n
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar
.
Speed increase when I did that was minimal but any speed increase is welcome
d
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries
.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far
.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> to
> take
> SELECT
> it's
> major
> Even
> since
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>sql
Question about optimization
Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Even
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar.
Speed increase when I did that was minimal but any speed increase is welcomed
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> > Hi All,
> >
> > I know that SQL server cursors are general a bad idea and the first thing
> to
> > go when optimizing. They're heavy and slow and generally avoidable. On
> > occasion it can happen that you get stuck with an SP that you need to
> > optimize and moving it to dynamic SQL is not an option (even though speed
> > wise it would be better). So here's my question for all those DB experts.
> >
> > I have logic that requires me to get a list of rows from one table and
> take
> > each row and execute another SP using that rows data. Which is faster
> >
> > 1. FAST_FORWARD CURSOR
> > OR
> > 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> > TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> >
> > The cursor is easier to understand code and seems like less queries but
> it's
> > a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> > performance differences so I'm hoping somebody knows the answer already.
> Even
> > with my small data, once I got the endless recursion solved, it seems like
> > the while loop might actually be faster (hard to tell though).
> >
> > Is there a 3 way to achieve this functionality?
> >
> > Oh yeah and I can't use the query analyzer to analyze the performance
> since
> > some of my inner sp's use temp tables (to avoid using CURSORS).
> >
> >
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Even
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar.
Speed increase when I did that was minimal but any speed increase is welcomed
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> > Hi All,
> >
> > I know that SQL server cursors are general a bad idea and the first thing
> to
> > go when optimizing. They're heavy and slow and generally avoidable. On
> > occasion it can happen that you get stuck with an SP that you need to
> > optimize and moving it to dynamic SQL is not an option (even though speed
> > wise it would be better). So here's my question for all those DB experts.
> >
> > I have logic that requires me to get a list of rows from one table and
> take
> > each row and execute another SP using that rows data. Which is faster
> >
> > 1. FAST_FORWARD CURSOR
> > OR
> > 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> > TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> >
> > The cursor is easier to understand code and seems like less queries but
> it's
> > a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> > performance differences so I'm hoping somebody knows the answer already.
> Even
> > with my small data, once I got the endless recursion solved, it seems like
> > the while loop might actually be faster (hard to tell though).
> >
> > Is there a 3 way to achieve this functionality?
> >
> > Oh yeah and I can't use the query analyzer to analyze the performance
> since
> > some of my inner sp's use temp tables (to avoid using CURSORS).
> >
> >
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>
Saturday, February 25, 2012
Question - SQL - Help - Pleased
Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
ESTUVO_univoldev,ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc),SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,
ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.
Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettXe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Xhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
> ESTUVO_univoldev,ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc),SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,
> ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
ESTUVO_univoldev,ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc),SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,
ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.
Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettXe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Xhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
> ESTUVO_univoldev,ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc),SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,
> ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
Question - SQL - Help - Pleased
Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTU
BA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc
),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal)
,
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valba
ldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantf
unrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costl
ocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numer
o), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_
nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTU
VO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquett_e.asp?id=5006 and
example data as insert statements [url]http://vyaskn.tripod.com/code._htm#inserts[/url
]
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmente
d
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, EST
UBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impulo
c),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal
),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valb
aldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cant
funrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_cost
locrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_nume
ro), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, EST
UVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultatio
n
> is for the Harward of it schemes it but well I think that it is for some b
ad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTU
BA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc
),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal)
,
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valba
ldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantf
unrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costl
ocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numer
o), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_
nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTU
VO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquett_e.asp?id=5006 and
example data as insert statements [url]http://vyaskn.tripod.com/code._htm#inserts[/url
]
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmente
d
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, EST
UBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impulo
c),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal
),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valb
aldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cant
funrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_cost
locrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_nume
ro), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, EST
UVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultatio
n
> is for the Harward of it schemes it but well I think that it is for some b
ad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
Question - SQL - Help - Pleased
Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettÂe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Âhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettÂe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Âhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.
Subscribe to:
Posts (Atom)