Handle Error: complicated

Showing posts with label complicated. Show all posts

Friday, March 30, 2012

Having trouble implementing complicated business rules.

The company I work for is a managed healthcare organization. One
particular metric we use for determining compensation for a medical
practice in our network is tracking the number of times a particular
practice sees a particular patient (member of the healthcare network).
Practices are awarded one "point" for each such occurrance within a
given timeframe, and compensated according to the number of total
points they accumulate for all network members.
As such, each month, I must take the medical claims filed for that
month and traverse through them in chronological order for each
member/practice combination, a "point" for each instance, but only one
per combination in, for example, a three month period. Therefore, if
practice A sees member 1 on 2006-01-01, then practice A would be
awarded one point on 2006-01-01, and would not be eligible for another
point for seeing member 1 until 2006-04-01.
Traversing the ordered data set is the easy part. I utilize a cursor,
note the last point date for each member/practice combination involved,
and then loop through new claims, awarding points for any claim that is
at least three months from the date the last point was awarded. The
problem I am having is correctly building that data set (cursor). This
is due to certain specific business rules I will list below. Here is a
simplified representation of the data involved:
-- Medical Practices:
CREATE TABLE Practices
(
PracticeID VARCHAR(6),
SpecialtyID INT
)
INSERT Practices VALUES ('A', 10)
INSERT Practices VALUES ('B', 10)
INSERT Practices VALUES ('C', 10)
INSERT Practices VALUES ('D', 21)
INSERT Practices VALUES ('E', 21)
INSERT Practices VALUES ('F', 21)
INSERT Practices VALUES ('G', 45)
-- Practices Splits (occasionally, one practice
-- will split up into multiple newer practices,
-- or multiple practices will merge into one newer
-- practice):
CREATE TABLE PracticeSplits
(
PracticeID_OLD VARCHAR(6),
PRacticeID_NEW VARCHAR(6)
)
INSERT PracticeSplits VALUES ('A', 'B')
INSERT PracticeSplits VALUES ('A', 'C')
INSERT PracticeSplits VALUES ('D', 'F')
INSERT PracticeSplits VALUES ('E', 'F')
-- SpecialtyExceptions (will explain below):
CREATE TABLE SpecialtyExceptions
(
SpecialtyID INT
)
INSERT SpecialtyExceptions VALUES (10)
-- Medical Claims:
CREATE TABLE Claims
(
ClaimID VARCHAR(16),
MemberID INT,
PracticeID VARCHAR(6),
VisitDate DATETIME
)
INSERT Claims VALUES ('200600001', 1, 'A', '2006-01-01')
INSERT Claims VALUES ('200600002', 1, 'A', '2006-02-11')
INSERT Claims VALUES ('200600003', 1, 'A', '2006-03-01')
INSERT Claims VALUES ('200600004', 1, 'A', '2006-03-30')
INSERT Claims VALUES ('200600005', 2, 'A', '2006-02-01')
INSERT Claims VALUES ('200600006', 2, 'B', '2006-01-01')
INSERT Claims VALUES ('200600007', 2, 'B', '2006-01-01')
INSERT Claims VALUES ('200600008', 2, 'C', '2006-03-01')
INSERT Claims VALUES ('200600009', 3, 'A', '2006-02-01')
INSERT Claims VALUES ('2006000010', 3, 'c', '2006-02-21')
INSERT Claims VALUES ('2006000011', 4, 'D', '2006-02-24')
INSERT Claims VALUES ('2006000012', 4, 'E', '2006-03-01')
INSERT Claims VALUES ('2006000013', 4, 'E', '2006-04-26')
INSERT Claims VALUES ('2006000014', 5, 'F', '2006-01-11')
INSERT Claims VALUES ('2006000015', 5, 'A', '2006-05-11')
INSERT Claims VALUES ('2006000016', 5, 'G', '2006-05-14')
Here are the rules:
1) In determining the date of the last point awarded, practice
splits must be considered. In this scenario, if practice A was
awarded a point on '2006-01-01', then neither practice A nor
practices B nor C may receive a point for the next three months.
2) Certain specialties are handled as exceptions, in that only
one point shall be awarded to *any* practice of the same
specialty where that specialty is represented in the
SpecialtyExceptions table. In the above scenario, if practice A
is awarded a point for seeing member 1 on 2006-01-01, then no
other practice with a specialty ID of 10 may receive a point for
seeing member 1 until 2006-04-01.
PROBLEM: Produce a data set consisting of all claims for each
network member, for each practice that would reflect the following
data, thereby facilitating the awarding of visit points in
accordance with the aforementioned business rules.
ClaimID, MemberID, PracticeID, VisitDate, PriorMaxVisitDate,
SpecialtyID
If I've been unclear on anything, please let me know. The schema was
developed by a contractor some time ago. While I would be open to
suggestions on any possible improvements in that regard, I'm not
exactly itching to re-write any more of it that might be absolutely
necessary.A couple of questions...
First, regarding this "one point within a time frame" rule, can you
elaborate on that? Is it one point per quarter per member, or must there be
a 3 month window? If Member 1 has an appointment on 2006-02-17 and another
apointment on 2006-04-01, will you count the later appointment for the month
of April, or exclude it because there was already an appointment in the last
3 months? If you are expluding it, do you do so based on 3 calendar months,
or 90 days?
In your sample data, specialties and practice splits are equivilant.
Meaning all practices in specialty 10 (A,B,C) are split, and all practices
in Specialty 21 (D,E,F) are also split. Is this just coincidence in your
test data, or are practices always split for a given specialty? If so, both
the PracticeSplits and the SpecialtyExceptions are redundant. Also, in the
case of specialty 10 being an exception, because these practices are already
split, this is redundant. Again, if this is merely a coincidence then it is
fine.
If you really have redundant rules built into different tables, then you
will want to change this to minimize the extraneous data, and simplify the
rules. In the mean time, you should be able to get all this information in
a single select without looping through an ordered set.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148409474.980326.150370@.j33g2000cwa.googlegroups.com...
> The company I work for is a managed healthcare organization. One
> particular metric we use for determining compensation for a medical
> practice in our network is tracking the number of times a particular
> practice sees a particular patient (member of the healthcare network).
> Practices are awarded one "point" for each such occurrance within a
> given timeframe, and compensated according to the number of total
> points they accumulate for all network members.
> As such, each month, I must take the medical claims filed for that
> month and traverse through them in chronological order for each
> member/practice combination, a "point" for each instance, but only one
> per combination in, for example, a three month period. Therefore, if
> practice A sees member 1 on 2006-01-01, then practice A would be
> awarded one point on 2006-01-01, and would not be eligible for another
> point for seeing member 1 until 2006-04-01.
> Traversing the ordered data set is the easy part. I utilize a cursor,
> note the last point date for each member/practice combination involved,
> and then loop through new claims, awarding points for any claim that is
> at least three months from the date the last point was awarded. The
> problem I am having is correctly building that data set (cursor). This
> is due to certain specific business rules I will list below. Here is a
> simplified representation of the data involved:
> -- Medical Practices:
> CREATE TABLE Practices
> (
> PracticeID VARCHAR(6),
> SpecialtyID INT
> )
> INSERT Practices VALUES ('A', 10)
> INSERT Practices VALUES ('B', 10)
> INSERT Practices VALUES ('C', 10)
> INSERT Practices VALUES ('D', 21)
> INSERT Practices VALUES ('E', 21)
> INSERT Practices VALUES ('F', 21)
> INSERT Practices VALUES ('G', 45)
> -- Practices Splits (occasionally, one practice
> -- will split up into multiple newer practices,
> -- or multiple practices will merge into one newer
> -- practice):
> CREATE TABLE PracticeSplits
> (
> PracticeID_OLD VARCHAR(6),
> PRacticeID_NEW VARCHAR(6)
> )
> INSERT PracticeSplits VALUES ('A', 'B')
> INSERT PracticeSplits VALUES ('A', 'C')
> INSERT PracticeSplits VALUES ('D', 'F')
> INSERT PracticeSplits VALUES ('E', 'F')
> -- SpecialtyExceptions (will explain below):
> CREATE TABLE SpecialtyExceptions
> (
> SpecialtyID INT
> )
> INSERT SpecialtyExceptions VALUES (10)
> -- Medical Claims:
> CREATE TABLE Claims
> (
> ClaimID VARCHAR(16),
> MemberID INT,
> PracticeID VARCHAR(6),
> VisitDate DATETIME
> )
> INSERT Claims VALUES ('200600001', 1, 'A', '2006-01-01')
> INSERT Claims VALUES ('200600002', 1, 'A', '2006-02-11')
> INSERT Claims VALUES ('200600003', 1, 'A', '2006-03-01')
> INSERT Claims VALUES ('200600004', 1, 'A', '2006-03-30')
> INSERT Claims VALUES ('200600005', 2, 'A', '2006-02-01')
> INSERT Claims VALUES ('200600006', 2, 'B', '2006-01-01')
> INSERT Claims VALUES ('200600007', 2, 'B', '2006-01-01')
> INSERT Claims VALUES ('200600008', 2, 'C', '2006-03-01')
> INSERT Claims VALUES ('200600009', 3, 'A', '2006-02-01')
> INSERT Claims VALUES ('2006000010', 3, 'c', '2006-02-21')
> INSERT Claims VALUES ('2006000011', 4, 'D', '2006-02-24')
> INSERT Claims VALUES ('2006000012', 4, 'E', '2006-03-01')
> INSERT Claims VALUES ('2006000013', 4, 'E', '2006-04-26')
> INSERT Claims VALUES ('2006000014', 5, 'F', '2006-01-11')
> INSERT Claims VALUES ('2006000015', 5, 'A', '2006-05-11')
> INSERT Claims VALUES ('2006000016', 5, 'G', '2006-05-14')
> Here are the rules:
> 1) In determining the date of the last point awarded, practice
> splits must be considered. In this scenario, if practice A was
> awarded a point on '2006-01-01', then neither practice A nor
> practices B nor C may receive a point for the next three months.
> 2) Certain specialties are handled as exceptions, in that only
> one point shall be awarded to *any* practice of the same
> specialty where that specialty is represented in the
> SpecialtyExceptions table. In the above scenario, if practice A
> is awarded a point for seeing member 1 on 2006-01-01, then no
> other practice with a specialty ID of 10 may receive a point for
> seeing member 1 until 2006-04-01.
> PROBLEM: Produce a data set consisting of all claims for each
> network member, for each practice that would reflect the following
> data, thereby facilitating the awarding of visit points in
> accordance with the aforementioned business rules.
> ClaimID, MemberID, PracticeID, VisitDate, PriorMaxVisitDate,
> SpecialtyID
> If I've been unclear on anything, please let me know. The schema was
> developed by a contractor some time ago. While I would be open to
> suggestions on any possible improvements in that regard, I'm not
> exactly itching to re-write any more of it that might be absolutely
> necessary.
>|||Jim Underwood wrote:
> A couple of questions...
> First, regarding this "one point within a time frame" rule, can you
> elaborate on that? Is it one point per quarter per member, or must there
be
> a 3 month window? If Member 1 has an appointment on 2006-02-17 and anothe
r
> apointment on 2006-04-01, will you count the later appointment for the mon
th
> of April, or exclude it because there was already an appointment in the la
st
> 3 months? If you are expluding it, do you do so based on 3 calendar month
s,
> or 90 days?
It is based on a three month window and pertains to a particular member
visit with a particular practice. For example, of the claims reviewed
for the current month, a particular practice could be awarded ten visit
points provided they saw ten different members, none of whom had
visited that practice (or any split-related practice, or any specialty
excepted practice) within the past three months.

> In your sample data, specialties and practice splits are equivilant.
> Meaning all practices in specialty 10 (A,B,C) are split, and all practices
> in Specialty 21 (D,E,F) are also split. Is this just coincidence in your
> test data, or are practices always split for a given specialty? If so, bo
th
> the PracticeSplits and the SpecialtyExceptions are redundant. Also, in th
e
> case of specialty 10 being an exception, because these practices are alrea
dy
> split, this is redundant. Again, if this is merely a coincidence then it
is
> fine.
It is, in fact, merely coincidental of my hastily compiled test data.
There is no relationship between practice splits and specialties.

> If you really have redundant rules built into different tables, then you
> will want to change this to minimize the extraneous data, and simplify the
> rules. In the mean time, you should be able to get all this information i
n
> a single select without looping through an ordered set.
Well, there are additional requirements that prevent handling this
through query. For starters, it is possible for a claim to be reversed
(voided), thereby rendering all visit points from the visit date on
that claim forward for that practice/member invalid, requiring the
process to reconsider and reestablish visit points for all subsequent
claim data.
I *would* be interested to know how I might be able to establish these
visit points on a one-per-three-month basis through standard query
while adhering to these business rules, though. Maybe I *could* make
that work after all.|||OK, this solution may be more complicated than it needs to be, but I think I
was able to get the results as a distinct list of "point worthy" encounters.
If the list is accurate, you can just change the select clause to
PracticeID, Count(*)
and group by practiceID
I created a cross referencing view on Practice Splits so that D and E would
be mutually exclusive, along with B and C. I assumed this is how you wanted
it to work, just remove the view and access the table directly if this is
not the case.
I also assumed that you wanted results by quarter, or rather wanted to
exclude points based on an earlier encounter with the same patient in the
particular quarter. I used 4 datetime variables, 2 to control the time
frame for which you are counting points, and 2 to control the timeframe for
which you are checking to see if points were already awarded.
You may be better off looping through this data in procedural code, if that
procedural code is much easier to follow. The multiple not exists and
subqueries make this a little ugly.
/*
Create a view to cross reference practice splits using the transitive
property
if A splits with B and A splits with C then B will split with C
This also duplicates the date with columns reversed for ease of joining
later on
*/
create view PracticeSplit_vw as
select b.PracticeID_NEW as PracticeID_OLD, c.PracticeID_NEW
from PracticeSplits a
inner join PracticeSplits b
on a.PracticeID_OLD = b.PracticeID_OLD
inner join PracticeSplits c
on b.PracticeID_OLD = c.PracticeID_OLD
and b.PracticeID_new <> c.practiceID_new
union
select b.PracticeID_OLD as PracticeID_OLD, c.PracticeID_OLD
from PracticeSplits a
inner join PracticeSplits b
on a.PracticeID_NEW = b.PracticeID_NEW
inner join PracticeSplits c
on b.PracticeID_NEW = c.PracticeID_NEW
and b.PracticeID_OLD <> c.PracticeID_OLD
union
select A.PracticeID_OLD, a.PracticeID_NEW
from PracticeSplits a
union
select A.PracticeID_NEW, a.PracticeID_OLD
from PracticeSplits a;
go
Declare @.PeriodStart as datetime
Declare @.PeriodEnd as datetime
Declare @.QuarterStart as datetime
Declare @.QuarterEnd as datetime
set @.PeriodStart = '2006-01-01'
set @.PeriodEnd = '2006-04-01'
set @.QuarterStart = '2006-01-01'
set @.QuarterEnd = '2006-04-01'
Select *
from practices as Prac
inner join claims as Claim
on Prac.PracticeID = Claim.PracticeID
where Claim.VisitDate >= @.PeriodStart
and Claim.VisitDate < @.PeriodEnd
/*
exclude patients already seen this period by this provider
*/
and not exists
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and claim1.PracticeID = claim.PracticeID
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)
and not exists
/*
exclude patients for Practice Splits
*/
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and claim1.PracticeID in
(
select split.PracticeID_NEW
from PracticeSplit_vw as split
where split.PracticeID_OLD = claim.PracticeID
)
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)
and not exists
/*
exclude patients for specialty exceptions
*/
(
select 1 from claims as Claim1
where
(
Claim1.VisitDate < Claim.VisitDate
or
(
Claim1.VisitDate = Claim.VisitDate
and claim1.claimID < claim.claimID
)
)
and claim1.memberID = claim.memberID
and Prac.SpecialtyID in
(
select spec.SpecialtyID
from SpecialtyExceptions as spec
where spec.SpecialtyID = Prac.SpecialtyID
)
and Claim1.VisitDate >= @.QuarterStart
and Claim1.VisitDate < @.QuarterEnd
)|||Take a look at what I posted and see if it is manageable (and if it works at
all on real data). If it works as it is, we may be able to add another
check to it to account for voided claims. Actually, we can definately do
it, it just may be more trouble than it is worth.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148415117.269330.20150@.j73g2000cwa.googlegroups.com...
> Jim Underwood wrote:
there be
another
month
last
months,
> It is based on a three month window and pertains to a particular member
> visit with a particular practice. For example, of the claims reviewed
> for the current month, a particular practice could be awarded ten visit
> points provided they saw ten different members, none of whom had
> visited that practice (or any split-related practice, or any specialty
> excepted practice) within the past three months.
>
practices
your
both
the
already
it is
> It is, in fact, merely coincidental of my hastily compiled test data.
> There is no relationship between practice splits and specialties.
>
the
in
> Well, there are additional requirements that prevent handling this
> through query. For starters, it is possible for a claim to be reversed
> (voided), thereby rendering all visit points from the visit date on
> that claim forward for that practice/member invalid, requiring the
> process to reconsider and reestablish visit points for all subsequent
> claim data.
> I *would* be interested to know how I might be able to establish these
> visit points on a one-per-three-month basis through standard query
> while adhering to these business rules, though. Maybe I *could* make
> that work after all.
>|||This is similar to the approach I was taking, though I was using the
code to produce a cursor to scroll through and determine the visit
points. Also, as far as the practice splits go, if practice A splits
into practices B and C, there is no relationship between B and C with
regard to visit points.
One point of note, however, is that, although these points would be
tabulated every month, the "new" data may consist of claims that are
months apart, due to the nature of health care claims processing. We
may receive claims that were erroneously filed with the wrong payor(s)
and bounced around for some time before finally making it to us. As
such, there must be a means of handling the possibility of a particular
practice/patient combination being represented more than once in a
given month (new period), yet only receiving one point unless those
visits were actually more than, say, the pre-established period lenght
of three months apart. That was where I found the need for scrolling
through the cursor to compare all new claims chronologically. In
reviewing your code, it appears that visit points would be awarded to
*every* new practice/patient combination for which a previous instance
had not been represented within that given timeframe.
For the sake of clarity, I haven't provided the entire scope for this
problem, though the missing pieces aren't part of the core process. For
example, the time period in question is actually dependent upon the
specialty of the practice - some are three months, but most are six.
This is referenced through a one-to-one relationship with a Specialties
table.
You have, however, brought to light a couple of areas where I could
possibly have been more efficient. I will try and incorporate those
approaches into the current process. I thank you so much for your time
to this point. You have been extremely helpful.|||Your rules do sound quite complex. While I am certain they could be
resolved with straight SQL, procedural code will probably work better,
simply because it will be easier to follow. The effort that would go into
making it work with straight SQL probably would not be worth it, and no one
would be able to maintain the resulting SQL should problems arise.
Thank you for clarifying how practice splits work. It makes that particular
function a little simpler, although your other requirements certainly
complicate things. Once you get all your logic working, you might post the
final code here and see if anyone can consolidate it, just for kicks. I am
rather interested in seeing your final results.
"Richard Carpenter" <rumbledor@.hotmail.com> wrote in message
news:1148564860.790963.269430@.38g2000cwa.googlegroups.com...
> This is similar to the approach I was taking, though I was using the
> code to produce a cursor to scroll through and determine the visit
> points. Also, as far as the practice splits go, if practice A splits
> into practices B and C, there is no relationship between B and C with
> regard to visit points.
> One point of note, however, is that, although these points would be
> tabulated every month, the "new" data may consist of claims that are
> months apart, due to the nature of health care claims processing. We
> may receive claims that were erroneously filed with the wrong payor(s)
> and bounced around for some time before finally making it to us. As
> such, there must be a means of handling the possibility of a particular
> practice/patient combination being represented more than once in a
> given month (new period), yet only receiving one point unless those
> visits were actually more than, say, the pre-established period lenght
> of three months apart. That was where I found the need for scrolling
> through the cursor to compare all new claims chronologically. In
> reviewing your code, it appears that visit points would be awarded to
> *every* new practice/patient combination for which a previous instance
> had not been represented within that given timeframe.
> For the sake of clarity, I haven't provided the entire scope for this
> problem, though the missing pieces aren't part of the core process. For
> example, the time period in question is actually dependent upon the
> specialty of the practice - some are three months, but most are six.
> This is referenced through a one-to-one relationship with a Specialties
> table.
> You have, however, brought to light a couple of areas where I could
> possibly have been more efficient. I will try and incorporate those
> approaches into the current process. I thank you so much for your time
> to this point. You have been extremely helpful.
>

Having Stored Procedure Problem - SQLExpress 2005

Hi.

I am writing a stored procedure for executing search
and returning results. The Sproc is a bit complicated and has
gotten away from me, now I can't understand why I am getting
bad results.

Its long and detailed so, before I dump code and get yelled at,
I am wondering if it is OK to post a large SPROC here for general
debugging help?

Thanks.

Absolutely that's ok.

It would also be helpful if you posted any ddl, sample data, and desired results as well.

|||

Yes, but ...

(And thanks for asking in advance!)

Before you do that, let me offer the following suggestions.

Often, it is best to 'build' a complicated procedure the way you build a structure, 'brick by brick'. Start out with a small part, get that working. Then add another small part, get that working.

When you are baffled, put in PRINT statements, printing the parameter and variable values, and even location in the code (for IF/WHILE switches) for verification.

And when you are ready to drop the whole thing here, please post the DDL for all associated tables, fabricate some sample data for those tables (in the form of INSERT statements), and a clear explanation of and example of desired output. See this link for help in putting it all together. The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you.

By going to that effort to prepare your 'presentation', the volunteers here can more easily get involved with your problem. If if is necessary to take the time to create a simulated environment just to try to help you, you will radically reduce the number of folks willing to make the time investment.

All that said, it is a great group of folks here, willing to help those willing to be helped.

|||

Concur 100% with Dale. Much better to give too much information. What will get you "yelled" at is asking a quesiton like:

My stored proc won't work. Why?

And yes, that is far too true of an example

Monday, March 19, 2012

Hardware Tuning for Queries

Hi,
I have several queries in a complicated database that join almost 10 tables
each. The query plans are good because there are a lot of index seeks and
merge/loop joins (Hash join have been eliminated by appropriate indexes). It
seems it doesn't lack any index. But as an example, a particular query that
joins 12 tables and returns 136,000 rows almost takes about 40 seconds in
best time (sometimes 100 seconds). I believe that index/query tuning cannot
make this query better, only hardware upgrade can help. I'd like to know
upgrade to which hardware components can help such queries?
Some information that might help:
CPU: 2
Memory: 1GB
Disk: 1
Cache Hit Ratio: above 99%
Concurrent Connections: 40-50
It is mentionable that there is no bottleneck while monitoring with
Performance Monitor in any hardware component like CPU, Disk, Memory. Also
this query doesn't have complex where condition, but it has a lot of CASE in
columns.
Any help would be greatly appreciated,
LeilaI cant really speak to the hard ware side of things but as a practice for
large joins like this I like to use Indexed Views and query the views
intead.
Read this article for some insight...
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Indexed views might not be the answer. As I understand it, the benefits
that indexed views (and partitioned tables) provide are only available
within the Developer and Enterprise Editions of SQL Server. It is possible
to create an indexed view within other versions of SQL Server, but the query
optimizer cannot use the index.
If I am mistaken please correct me!
--
Keith Kratochvil
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||The engine considers the index on the view automatically for Ent/Dev
edition. For other editions, you need NoExpand hint to get the indexed view
to be considered.
e.g.
select *
from indexedview with (noexpand)
--
-oj
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ZPfWY$5GHA.2464@.TK2MSFTNGP06.phx.gbl...
> Indexed views might not be the answer. As I understand it, the benefits
> that indexed views (and partitioned tables) provide are only available
> within the Developer and Enterprise Editions of SQL Server. It is
> possible to create an indexed view within other versions of SQL Server,
> but the query optimizer cannot use the index.
> If I am mistaken please correct me!
> --
> Keith Kratochvil
>
> "Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
> news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>>I cant really speak to the hard ware side of things but as a practice for
>>large joins like this I like to use Indexed Views and query the views
>>intead.
>> Read this article for some insight...
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>|||Because of outer joins, Indexed View cannot be used
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:uo5zHd%235GHA.4116@.TK2MSFTNGP03.phx.gbl...
>I cant really speak to the hard ware side of things but as a practice for
>large joins like this I like to use Indexed Views and query the views
>intead.
> Read this article for some insight...
> http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||> Memory: 1GB
This is a bit low by today's standards. More memory can reduce disk i/o.
> Disk: 1
So you have only one disk drive on the server? No RAID? Data and log on
the same drive? I would at least add disks to ensure fault tolerance and
separate data and log files.
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory.
What is the physical disk transfers/sec and bytes/sec on the disk? Note
that a disk bottleneck will not necessarily manifest itself as queue time.
You need to know the transfers/sec your particular disk can sustain in order
to determine whether or not you have a disk bottleneck.
> But as an example, a particular query that joins 12 tables and returns
> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
> seconds).
What is your maximum response time requirement for this query? 136,000 rows
is a lot of data to return and client-site processing of large data volumes
like this can be more significant than query execution time. It would be
embarrassing to throw server hardware at a performance problem that turns
out to be on the client side ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have several queries in a complicated database that join almost 10
> tables each. The query plans are good because there are a lot of index
> seeks and merge/loop joins (Hash join have been eliminated by appropriate
> indexes). It seems it doesn't lack any index. But as an example, a
> particular query that joins 12 tables and returns 136,000 rows almost
> takes about 40 seconds in best time (sometimes 100 seconds). I believe
> that index/query tuning cannot make this query better, only hardware
> upgrade can help. I'd like to know upgrade to which hardware components
> can help such queries?
> Some information that might help:
> CPU: 2
> Memory: 1GB
> Disk: 1
> Cache Hit Ratio: above 99%
> Concurrent Connections: 40-50
> It is mentionable that there is no bottleneck while monitoring with
> Performance Monitor in any hardware component like CPU, Disk, Memory. Also
> this query doesn't have complex where condition, but it has a lot of CASE
> in columns.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed Dan!
Today I discovered very strange thing that almost solved the problem! We
added another 512MB memory to the server, now it has 1.5GB. I tried that
particular query again, the result was the same, about 40 seconds. I stopped
the SQL Server service and restarted it manually. I couldn't believe the
result! The query took 11 seconds at first try, and when cached it stayed on
8 seconds for several tries! I restarted the windows again, and then tried
the query after booting; again 40 seconds. Then manually stopped and
restarted SQL Server service and it gained 8 seconds again!
I monitored the memory consumed by SQL Server. Before manual restart of
service, SQL Server consumed memory very slowly, and it took 40 seconds to
fill about 120MB of RAM. After manual restart, the memory consumed by SQL
Server(up to 120MB) was growing too fast (8 seconds)!
I have no idea that why a manual restart of service must have this effect! I
am suspected that it can be a bug in Windows 2003.
However I must take this server back to production environment to test it
under load of users.
Any ideas?...
Thanks again,
Leila
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
> So you have only one disk drive on the server? No RAID? Data and log on
> the same drive? I would at least add disks to ensure fault tolerance and
> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
> What is the physical disk transfers/sec and bytes/sec on the disk? Note
> that a disk bottleneck will not necessarily manifest itself as queue time.
> You need to know the transfers/sec your particular disk can sustain in
> order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
> What is your maximum response time requirement for this query? 136,000
> rows is a lot of data to return and client-site processing of large data
> volumes like this can be more significant than query execution time. It
> would be embarrassing to throw server hardware at a performance problem
> that turns out to be on the client side ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by appropriate
>> indexes). It seems it doesn't lack any index. But as an example, a
>> particular query that joins 12 tables and returns 136,000 rows almost
>> takes about 40 seconds in best time (sometimes 100 seconds). I believe
>> that index/query tuning cannot make this query better, only hardware
>> upgrade can help. I'd like to know upgrade to which hardware components
>> can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot of
>> CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>|||You might take a look at the execution plans. I would expect these to be
the same with the same query/data but the only way to make sure is to check.
Given the same plans, resource contention is the most likely cause. I
suggest you monitor CPU and disk to see if you can spot what the contention
might be during slowness.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:egcODSL6GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Thanks indeed Dan!
> Today I discovered very strange thing that almost solved the problem! We
> added another 512MB memory to the server, now it has 1.5GB. I tried that
> particular query again, the result was the same, about 40 seconds. I
> stopped the SQL Server service and restarted it manually. I couldn't
> believe the result! The query took 11 seconds at first try, and when
> cached it stayed on 8 seconds for several tries! I restarted the windows
> again, and then tried the query after booting; again 40 seconds. Then
> manually stopped and restarted SQL Server service and it gained 8 seconds
> again!
> I monitored the memory consumed by SQL Server. Before manual restart of
> service, SQL Server consumed memory very slowly, and it took 40 seconds to
> fill about 120MB of RAM. After manual restart, the memory consumed by SQL
> Server(up to 120MB) was growing too fast (8 seconds)!
> I have no idea that why a manual restart of service must have this effect!
> I am suspected that it can be a bug in Windows 2003.
> However I must take this server back to production environment to test it
> under load of users.
> Any ideas?...
> Thanks again,
> Leila
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pqMKZH6GHA.940@.TK2MSFTNGP03.phx.gbl...
>> Memory: 1GB
>> This is a bit low by today's standards. More memory can reduce disk i/o.
>> Disk: 1
>> So you have only one disk drive on the server? No RAID? Data and log on
>> the same drive? I would at least add disks to ensure fault tolerance and
>> separate data and log files.
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> What is the physical disk transfers/sec and bytes/sec on the disk? Note
>> that a disk bottleneck will not necessarily manifest itself as queue
>> time. You need to know the transfers/sec your particular disk can sustain
>> in order to determine whether or not you have a disk bottleneck.
>> But as an example, a particular query that joins 12 tables and returns
>> 136,000 rows almost takes about 40 seconds in best time (sometimes 100
>> seconds).
>> What is your maximum response time requirement for this query? 136,000
>> rows is a lot of data to return and client-site processing of large data
>> volumes like this can be more significant than query execution time. It
>> would be embarrassing to throw server hardware at a performance problem
>> that turns out to be on the client side ;-)
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:OOuN1Q%235GHA.3292@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have several queries in a complicated database that join almost 10
>> tables each. The query plans are good because there are a lot of index
>> seeks and merge/loop joins (Hash join have been eliminated by
>> appropriate indexes). It seems it doesn't lack any index. But as an
>> example, a particular query that joins 12 tables and returns 136,000
>> rows almost takes about 40 seconds in best time (sometimes 100 seconds).
>> I believe that index/query tuning cannot make this query better, only
>> hardware upgrade can help. I'd like to know upgrade to which hardware
>> components can help such queries?
>> Some information that might help:
>> CPU: 2
>> Memory: 1GB
>> Disk: 1
>> Cache Hit Ratio: above 99%
>> Concurrent Connections: 40-50
>> It is mentionable that there is no bottleneck while monitoring with
>> Performance Monitor in any hardware component like CPU, Disk, Memory.
>> Also this query doesn't have complex where condition, but it has a lot
>> of CASE in columns.
>> Any help would be greatly appreciated,
>> Leila
>>
>

Hardware Tuning for Queries

Wednesday, March 7, 2012

hard time understanding the execution plans

SQL server 2005 has a really complicated execution plans that I am really unable to compare two execution plans with each other cumulative.

I can easliy see by eye that one execution plan is slower than the other but is there anyway to compare two execution plans in sql 2005?

Hi,

I'm afraid there is no comparison tool. Here is a reference to the items in the Execution Plan: http://msdn2.microsoft.com/en-us/library/ms175913.aspx. Hope it helps.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||True, either you can export the text based to a file for a comparison or search for any third party tools on web in this regard.|||

my problem is query execution plan does not help me understand the bottlenecks in the system as I can't compare with any realistic thing against.

how do u guys solve this problem? How do u decide that this is a decend query or say this is a bad one?

|||Here are some tips:
Remember that the thickness of arrows hints how many rows are transferred|||

thanks for the tips they are GREAT.

I will look into statistics IO

Friday, March 30, 2012

Having trouble implementing complicated business rules.

Having Stored Procedure Problem - SQLExpress 2005

Monday, March 19, 2012

Hardware Tuning for Queries

Hardware Tuning for Queries

Hardware Tuning for Queries

Wednesday, March 7, 2012

hard time understanding the execution plans

Handle Error

Blog Archive

About Me