Query Syntax: becomes

Showing posts with label becomes. Show all posts

Wednesday, March 28, 2012

query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakriWould the answer in this case be 650?|||it will be 650 and 700 according to user id|||Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).|||Ok - you edited your typo while I was posting :D

So - why two answers?|||ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.|||Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH|||i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.|||What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

-PatP

Monday, February 20, 2012

Query suddenly becomes very slow

Hi. This is a bit of an open ended question which I don't think there is a
single answer to but I woud appreciate suggestions on though. For the last
few months a stored procedure has been taking between 2-12 seconds to
complete. It runs several times a day. Suddenly today in the space of a
minute the stored procedure went from 2 seconds to complete to 35 minutes!
It was inexplicable. There was nothing else running on the machine (that I
know of) at the time. Further invocations continued to take at least 10
minutes to run. Only after bouncing the SQL Server instance did the stored
procedure go back to its normal timeframe of 2-12 seconds.
Nothing seemed out of the ordinary - it was just a regular day's processing
as had been many times in the past. How can there be such a huge decrease in
performance so rapidly? What could have caused it?
--
McGeeky
http://mcgeeky.blogspot.comhave you updated you table statistics?
and defrag your indexes
"McGeeky" <anon@.anon.com> wrote in message
news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Hi. This is a bit of an open ended question which I don't think there is a
> single answer to but I woud appreciate suggestions on though. For the last
> few months a stored procedure has been taking between 2-12 seconds to
> complete. It runs several times a day. Suddenly today in the space of a
> minute the stored procedure went from 2 seconds to complete to 35 minutes!
> It was inexplicable. There was nothing else running on the machine (that I
> know of) at the time. Further invocations continued to take at least 10
> minutes to run. Only after bouncing the SQL Server instance did the stored
> procedure go back to its normal timeframe of 2-12 seconds.
> Nothing seemed out of the ordinary - it was just a regular day's
> processing
> as had been many times in the past. How can there be such a huge decrease
> in
> performance so rapidly? What could have caused it?
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||The database is configured to auto update stats.
Would not updating stats cause performance to drop off a cliff all of a
sudden?
--
McGeeky
http://mcgeeky.blogspot.com
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:#v41#IVSGHA.3192@.TK2MSFTNGP09.phx.gbl...
> have you updated you table statistics?
> and defrag your indexes
> "McGeeky" <anon@.anon.com> wrote in message
> news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
> > Hi. This is a bit of an open ended question which I don't think there is
a
> > single answer to but I woud appreciate suggestions on though. For the
last
> > few months a stored procedure has been taking between 2-12 seconds to
> > complete. It runs several times a day. Suddenly today in the space of a
> > minute the stored procedure went from 2 seconds to complete to 35
minutes!
> > It was inexplicable. There was nothing else running on the machine (that
I
> > know of) at the time. Further invocations continued to take at least 10
> > minutes to run. Only after bouncing the SQL Server instance did the
stored
> > procedure go back to its normal timeframe of 2-12 seconds.
> >
> > Nothing seemed out of the ordinary - it was just a regular day's
> > processing
> > as had been many times in the past. How can there be such a huge
decrease
> > in
> > performance so rapidly? What could have caused it?
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>|||auto update is not enough.
a good and planned cleansing is always better.
1 client delete and reload some tables at regular basis, if we don't update
the statistics a query against these table drop from seconds to minutes.
so updating stats and defrag indexes is really important.
regarding which way you use to defrag your indexes, the system will also
update the stats at the same time.
see the BOL and search the web for more info
"McGeeky" <anon@.anon.com> wrote in message
news:utYEAPSTGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The database is configured to auto update stats.
> Would not updating stats cause performance to drop off a cliff all of a
> sudden?
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
> "Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
> news:#v41#IVSGHA.3192@.TK2MSFTNGP09.phx.gbl...
>> have you updated you table statistics?
>> and defrag your indexes
>> "McGeeky" <anon@.anon.com> wrote in message
>> news:OnY8TKUSGHA.5656@.TK2MSFTNGP11.phx.gbl...
>> > Hi. This is a bit of an open ended question which I don't think there
>> > is
> a
>> > single answer to but I woud appreciate suggestions on though. For the
> last
>> > few months a stored procedure has been taking between 2-12 seconds to
>> > complete. It runs several times a day. Suddenly today in the space of a
>> > minute the stored procedure went from 2 seconds to complete to 35
> minutes!
>> > It was inexplicable. There was nothing else running on the machine
>> > (that
> I
>> > know of) at the time. Further invocations continued to take at least 10
>> > minutes to run. Only after bouncing the SQL Server instance did the
> stored
>> > procedure go back to its normal timeframe of 2-12 seconds.
>> >
>> > Nothing seemed out of the ordinary - it was just a regular day's
>> > processing
>> > as had been many times in the past. How can there be such a huge
> decrease
>> > in
>> > performance so rapidly? What could have caused it?
>> >
>> > --
>> > McGeeky
>> > http://mcgeeky.blogspot.com
>> >
>> >
>> >
>>
>

Wednesday, March 28, 2012

query without using cursor

Monday, February 20, 2012

Query suddenly becomes very slow

Query suddenly becomes very slow

Query suddenly becomes very slow

Query Syntax

Blog Archive

About Me