"Stock Recon" type query .. most efficient route?
-
Hi All I thought I'd put this to you, my fellow devs, as I just cant decide the most efficient route! :confused: Suppose I have 2 tables, 1: id, productid, datedelivered, qty AND 2: id, productid, datesold, qty I would like to end up having a result with: productid, monthvalue, openingbal, delivered, sold, closingbal (monthvalue being something like 2008-01-01, i.e. each like is the stock recon for the month) Now I know there are a few ways of doing this, one I thought of is with a cursor. But not being too clued up with the new features that 2005 provides I am not sure what the most efficient way would be ... any ideas? :doh: Thanks.
-
Hi All I thought I'd put this to you, my fellow devs, as I just cant decide the most efficient route! :confused: Suppose I have 2 tables, 1: id, productid, datedelivered, qty AND 2: id, productid, datesold, qty I would like to end up having a result with: productid, monthvalue, openingbal, delivered, sold, closingbal (monthvalue being something like 2008-01-01, i.e. each like is the stock recon for the month) Now I know there are a few ways of doing this, one I thought of is with a cursor. But not being too clued up with the new features that 2005 provides I am not sure what the most efficient way would be ... any ideas? :doh: Thanks.
Francois Searle wrote:
one I thought of is with a cursor
Stored procedures would probably be better performance-wise.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Hi All I thought I'd put this to you, my fellow devs, as I just cant decide the most efficient route! :confused: Suppose I have 2 tables, 1: id, productid, datedelivered, qty AND 2: id, productid, datesold, qty I would like to end up having a result with: productid, monthvalue, openingbal, delivered, sold, closingbal (monthvalue being something like 2008-01-01, i.e. each like is the stock recon for the month) Now I know there are a few ways of doing this, one I thought of is with a cursor. But not being too clued up with the new features that 2005 provides I am not sure what the most efficient way would be ... any ideas? :doh: Thanks.
A cursor is probably one of the worst ways you could do this. You should always attempt, where possible, to use set based queries as they are what database engines are designed to do.
Deja View - the feeling that you've seen this post before.
-
Francois Searle wrote:
one I thought of is with a cursor
Stored procedures would probably be better performance-wise.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Yes, I agree it should be within a SP, but I was referring more to the SQL within the SP, the methodology as such.
-
A cursor is probably one of the worst ways you could do this. You should always attempt, where possible, to use set based queries as they are what database engines are designed to do.
Deja View - the feeling that you've seen this post before.
I know cursors are the worst way, but currently my attempt at doing it using "normal" sql queries is running too long. Surely this must be a common query? I mean there are so many scenarios where this type of "dataset" is needed.
-
Yes, I agree it should be within a SP, but I was referring more to the SQL within the SP, the methodology as such.
If you are going the standard sql route and it is slow, maybe query analyzer could shed some light for you where bottlenecks are.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon