Grabbing the Top Nth Records based on another table per group

Hi All,

I am trying to perform a SQl query that I have never attempted before.

I have a table with records such that would look like this

GroupField1 GroupField2 Field1 Amount

AAA BBB Field1Data 23.00

AAA BBB Feld2Data 24.00

AAA BBB Field3Data 8.00

BBB CCC Field1Data 25.00

BBB CCC Field2 Data 23.00

DDD BBH Field1Data 232.00

I have a second table that is like

GroupField1 GroupField2 NumberOfRows

AAA BBB 2

BBB CCC 1

DDD BBH 1

What I am trying to do is create a query that gets data from table 1 based on how many rows table 2 says to get.

I think the query would be something like

Select Top(select number of rows from table 2 where Table1.GroupField1 = Table2.GroupField1 and Table1.GroupField2 = Table2.GroupField2) from Table1

Anybody oput there have any expertise in this area?

Thank you,

Lynn

Wrong forum. Try posting on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Good query goes bad

Hello,
We have a 3rd party app that uses "sp_executesql" to run a stored procedure.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or two
given the same parameters. It seems to be doing a ton of I/O's when it's slow
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictable
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- Martin
We've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James

Good query goes bad

Hello,
We have a 3rd party app that uses "sp_executesql" to run a stored procedure
.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or tw
o
given the same parameters. It seems to be doing a ton of I/O's when it's slo
w
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictabl
e
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- MartinWe've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James

Good query goes bad

Hello,
We have a 3rd party app that uses "sp_executesql" to run a stored procedure.
It'll perform well (sub-second response) for anywhere from an hour to up to
a couple of weeks and then all of a sudden it'll start taking a minute or two
given the same parameters. It seems to be doing a ton of I/O's when it's slow
like this (no surprise).
It will stay bogged-down until I do an sp_recompile on one of the tables
involved (over 4 million rows) after which it'll be fine for an unpredictable
length of time.
And whenever I run the exact same query in Query Analyzer it is always fast;
even while a DTS package is running the same SQL slowly.
Any help would be appreciated.
- MartinWe've seen this many times; we would be the "third party" in your case. It
might be up to your "third party" to deal with this, depending on whether
it's the same issue; "parameter sniffing".
A procedure gets called "the first time" and then compiled using the
parameters passed at that time. If queries within the procedure use any of
the passed parameters, those queries re optimized using the values of the
parameters passed. Basically, that first compile is significant.
In our case, we could take an ID or a NULL for one particular parameter, and
we would branch and do two different things in the procedure depending on
ID/NULL. The problem was that all queries in the procedure that used that
parameter (regardless of whether the logic would have been called in that
case) are optimized for that value. In our case the plan used when a NULL
was passed was to scan an index. So if the first compile happened to be
done when a NULL was passed, all subsequent calls (even those where an ID
instead of NULL was passed) used the hideous "scan index" plan.
A better explanation might be found be searching on "parameter sniffing". A
cheesy quick test might be to add the "WITH RECOMPILE" hint to the procedure
in question and see if the problem goes away. (You won't want to leave that
hint because the forced recompile on each invocation does have a cost.)
And of course, it might be something completely different. But this
parameter sniffing thing has bitten us a few times. We are smarter now, and
queries within procedures that use parameters passed to those procedures
raise a RED FLAG with our team.
James