|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Optimize this queryHello,
I would like to optimize the following query. The goal is to give a record number on each row based on a row owner and a timestamp. UPDATE dbo.mytable SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and a.[timestamp] >= b.[timestamp]) FROM dbo.mytable a WHERE owner = N'me' AND rec_id IS NULL TIA. Olivier Matrot wrote:
> Hello, Assuming you have an index on myTable on columns (owner, timestamp),> I would like to optimize the following query. The goal is to give a record > number on each row based on a row owner and a timestamp. > UPDATE dbo.mytable > > SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and > a.[timestamp] >= b.[timestamp]) > > FROM dbo.mytable a WHERE owner = N'me' AND rec_id IS NULL then I think that query is pretty much as good as you can get it. Note that you don't need the FROM clause though in your UPDATE. i.e. UPDATE dbo.mytable SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE b.owner= dbo.mytable.owner AND b.[timestamp] < dbo.mytable.[timestamp] ) WHERE owner = N'me' AND rec_id IS NULL Ok thanks for that.
Could we imagine using a user function to get the next record number instead ? Would it be faster ? Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155201742.194301.111490@q16g2000cwq.googlegroups.com... > Olivier Matrot wrote: >> Hello, >> I would like to optimize the following query. The goal is to give a >> record >> number on each row based on a row owner and a timestamp. >> UPDATE dbo.mytable >> >> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and >> a.[timestamp] >= b.[timestamp]) >> >> FROM dbo.mytable a WHERE owner = N'me' AND rec_id IS NULL > > Assuming you have an index on myTable on columns (owner, timestamp), > then I think that query is pretty much as good as you can get it. > > Note that you don't need the FROM clause though in your UPDATE. i.e. > > UPDATE dbo.mytable > SET rec_id = (SELECT COUNT(*) > FROM dbo.mytable b > WHERE b.owner= dbo.mytable.owner > AND b.[timestamp] < dbo.mytable.[timestamp] ) > WHERE owner = N'me' > AND rec_id IS NULL > Olivier Matrot wrote:
> Ok thanks for that. If you are using SQL Server 2005 you could use the ROW_NUMBER() OVER()> Could we imagine using a user function to get the next record number instead > ? Would it be faster ? function to generate your sequence numbers. e.g. UPDATE dbo.mytable SET rec_id = b.RowNbr FROM dbo.mytable a INNER JOIN ( SELECT owner, [timestamp], RowNbr = ROW_NUMBER() OVER(PARTITION BY owner ORDER BY [timestamp]) FROM mytable WHERE owner = N'me' AND recid is NULL ) b ON b.owner = a.owner AND b.[timestamp] = a.[timestamp] WHERE a.owner = N'me' AND a.rec_id IS NULL (I haven't actually used SQL Server 2005 so excuse me if I got the syntax wrong!) That might be faster since it's not doing a co-related sub-query to work out the row numbers, but as you can see it's a more complicated query. Chris Chris,
Thanks for that. I'll give it a try. BTW the syntax is good !!!! Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155204321.292170.185540@m79g2000cwm.googlegroups.com... > Olivier Matrot wrote: >> Ok thanks for that. >> Could we imagine using a user function to get the next record number >> instead >> ? Would it be faster ? > > If you are using SQL Server 2005 you could use the ROW_NUMBER() OVER() > function to generate your sequence numbers. e.g. > > UPDATE dbo.mytable > SET rec_id = b.RowNbr > FROM dbo.mytable a > INNER JOIN ( > SELECT owner, > [timestamp], > RowNbr = ROW_NUMBER() > OVER(PARTITION BY > owner > ORDER BY > [timestamp]) > FROM mytable > WHERE owner = N'me' > AND recid is NULL > ) b > ON b.owner = a.owner > AND b.[timestamp] = a.[timestamp] > WHERE a.owner = N'me' > AND a.rec_id IS NULL > > (I haven't actually used SQL Server 2005 so excuse me if I got the > syntax wrong!) > > That might be faster since it's not doing a co-related sub-query to > work out the row numbers, but as you can see it's a more complicated > query. > > Chris > Hi
Does the query run slow or give a wrong output? UPDATE dbo.mytable SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and a.[timestamp] >= b.[timestamp]) WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS NULL) Show quote "Olivier Matrot" <olivier.matrot.rte@online.nospam> wrote in message news:Ojs$n0FvGHA.4140@TK2MSFTNGP03.phx.gbl... > Hello, > I would like to optimize the following query. The goal is to give a record > number on each row based on a row owner and a timestamp. > UPDATE dbo.mytable > > SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and > a.[timestamp] >= b.[timestamp]) > > FROM dbo.mytable a WHERE owner = N'me' AND rec_id IS NULL > > TIA. > Uri Dimant wrote:
> UPDATE dbo.mytable Wouldn't that update all rows in the table, instead of just the ones> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and > a.[timestamp] >= b.[timestamp]) > WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS > NULL) where owner = 'Me' and rec_id is null? Chris
NO, because in correlated subquery is a WHERE condition 'WHERE owner = N'me' AND rec_id IS NULL' Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155204424.696723.105920@b28g2000cwb.googlegroups.com... > Uri Dimant wrote: >> UPDATE dbo.mytable >> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and >> a.[timestamp] >= b.[timestamp]) >> WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id >> IS >> NULL) > > Wouldn't that update all rows in the table, instead of just the ones > where owner = 'Me' and rec_id is null? > Uri Dimant wrote:
> Chris But that isn't a correlated subquery! Is it? Am I missing something?> NO, because in correlated subquery is a WHERE condition 'WHERE owner = N'me' > AND rec_id IS NULL' Chris
Yes ,it is a corellated subquery.EXISTS simply checks for a nonempty set.It returns (internally) either TRUE or NOT TRUE Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155205073.143853.213620@75g2000cwc.googlegroups.com... > Uri Dimant wrote: >> Chris >> NO, because in correlated subquery is a WHERE condition 'WHERE owner = >> N'me' >> AND rec_id IS NULL' > > But that isn't a correlated subquery! Is it? Am I missing something? > Uri Dimant wrote:
> Chris How is this sub-query correlated to the outer query?> Yes ,it is a corellated subquery.EXISTS simply checks for a nonempty set.It > returns (internally) either TRUE or NOT TRUE > (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS Would this not return TRUE if there was one or more rows in mytable> NULL) with owner = 'Me' and rec_id null, and then update all rows in the outer query? > UPDATE dbo.mytable Chris> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and > a.[timestamp] >= b.[timestamp]) > WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS > NULL) Chris
For example you have the following query select <column list> from tabel_a where not exists ( select * from table_a a where a.col=table_a.col) NOT EXISTS correlated table_a to those matches ,looking for table_a that don't have a single row returned in the subquery The optimizer knows that as soon as it gets a single hit using NOT EXISTS the operation is TRUE or NOT TRUE and futher operation is unnecessary Think about correlated subquries as a loop in programming such as do ....while or for... The result of each execution of the subquery must be correlated to a row of the main query Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155206245.887708.326960@75g2000cwc.googlegroups.com... > Uri Dimant wrote: >> Chris >> Yes ,it is a corellated subquery.EXISTS simply checks for a nonempty >> set.It >> returns (internally) either TRUE or NOT TRUE > > How is this sub-query correlated to the outer query? > >> (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS >> NULL) > > Would this not return TRUE if there was one or more rows in mytable > with owner = 'Me' and rec_id null, and then update all rows in the > outer query? > >> UPDATE dbo.mytable >> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and >> a.[timestamp] >= b.[timestamp]) >> WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id >> IS >> NULL) > > Chris > Uri Dimant wrote:
> Think about correlated subquries as a loop in programming such as do Thanks, I'm aware of how correlated subqueries work. I still don't see> ...while or for... The result of each execution of the subquery must be > correlated to a row of the main query how your one is a correlated subquery though. Perhaps someone else could explain this to me? I don't have access to SQL Server right now otherwise I'd try it myself. Chris Chris
> Perhaps someone else could explain this to me? UPDATE dbo.mytableSET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and a.[timestamp] >= b.[timestamp]) WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS NULL) -------- EXISTS correlated dbo.mytable to those matches ,looking for dbo.mytable that do have a single row returned in the subquery Show quote "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155208333.091637.110730@h48g2000cwc.googlegroups.com... > Uri Dimant wrote: >> Think about correlated subquries as a loop in programming such as do >> ...while or for... The result of each execution of the subquery must be >> correlated to a row of the main query > > Thanks, I'm aware of how correlated subqueries work. I still don't see > how your one is a correlated subquery though. > > Perhaps someone else could explain this to me? I don't have access to > SQL Server right now otherwise I'd try it myself. > > Chris > Uri Dimant wrote:
> UPDATE dbo.mytable Still don't see the correlation. It's getting late, maybe someone will> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and > a.[timestamp] >= b.[timestamp]) > WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS > NULL) > > EXISTS correlated dbo.mytable > to those matches ,looking for dbo.mytable > that do have a single row returned in the subquery be able to explain this to me in the morning! That subquery cannot be correlated. Since the table referred to in the EXISTS subquery is the same
table which will be updates, the column names referred to (non-qualified, in this case) either doesn't exist in that table and an error is produced or refers to the table inside the subquery which mean no correlation to the outer query. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Chris Lim" <blackca***@hotmail.com> wrote in message news:1155209067.943185.249640@i3g2000cwc.googlegroups.com... > Uri Dimant wrote: >> UPDATE dbo.mytable >> SET rec_id = (SELECT COUNT(*) FROM dbo.mytable b WHERE owner= N'me' and >> a.[timestamp] >= b.[timestamp]) >> WHERE EXISTS (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS >> NULL) >> >> EXISTS correlated dbo.mytable >> to those matches ,looking for dbo.mytable >> that do have a single row returned in the subquery > > Still don't see the correlation. It's getting late, maybe someone will > be able to explain this to me in the morning! > Uri left out the alias (table prefix) "a" in his original post, making
the example hard to comment from there. A correlated example with EXISTS, straight from the SQL2K5 Impl. and maintenance, p.175: SELECT * FROM HumanResources.Employee AS E WHERE EXISTS ( SELECT * FROM HumanResources.EmployeeAddress EA WHERE E.EmployeeId = EA.EmployeeId ) As you all know, what makes this correlated is that the subquery correlates the outer query's EmployeeId value to the subquery's EmployeeId. Tibor just made a good point though. Cheers, Johan Sjöström MSc, MCP, MCAD Uri Dimant wrote:
> select <column list> from tabel_a where not exists ( select * from table_a In your above example, there is a link between table_a in your subquery> a where a.col=table_a.col) with table_a in your outer query. That is the correlation. Where is the equivalent link in this subquery? > (SELECT * FROM dbo.mytable WHERE owner = N'me' AND rec_id IS NULL) Chris |
|||||||||||||||||||||||
Other interesting topics