requesting query optimization help

so here are the tables, with a little info about each one:
create table areas (
areaid uniqueidentifier primary key not null, -- PK, clustered
areanumber int identity(1, 1) not null, -- indexed
areaname varchar(100) not null,
regionnumber int not null) -- FK to regions table PK
the areas table has about 16000 rows, and three unindexed columns not
listed here.
create table regions (
regionnumber int identity(1, 1) primary key not null, -- PK, clustered
regionname varchar(100) not null,
parentregionnumber int not null) -- FK to regions table PK
the regions table has about 800 rows, and about 12 columns not listed
here
create table regionrelationships (
regionnumber int primary key not null,
relatedregionnumber int primary key not null) -- PK is clustered
the regionrelationships table has about 1700 rows, and no unlisted
columns
and here are the various versions of the stored procedure query i'm
trying to optimize with the execution plan costs listed with each one.
the original stored procedure body looked liked this:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
ORDER BY a.areaname
cost: .205
then business rules caused a change in the stored procedure body, and
the cost went way up:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56 OR a.regionnumber IN (
SELECT relatedregionnumber
FROM regionrelationships
WHERE regionnumber = 56)
ORDER BY a.areaname
cost: .518
i've tweaked a bunch of stuff, and this is the best i've been able to
do so far:
SELECT DISTINCT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
LEFT JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE a.regionnumber = 56 OR r.regionnumber = 56
ORDER BY areaname
cost: .417
most of the remaining cost is in a hash right outer join (35%, mostly
CPU) and a cluster scan on areas (44%, mostly I/O)
i've tried a lot of things, including:
* using the areanumber as the primary key of the areas table instead of
the areaid, to make the cluster index smaller, to try to make cluster
scans faster (no effect at all)
* creating clustered indexes on columns other than the primary keys,
such as the regionrelationships regionnumber column, to try to make
hash scans faster (no effect at all)
so i'm just wondering, i'm new to this aspect of optimization. is there
anything here that stands out as something i can do to improve the
performance of this stored procedure? hopefully i've provided enough
information.
thanks in advance for any help
jasonOn 15 Mar 2006 13:45:49 -0800, jason wrote:
(snip)
>then business rules caused a change in the stored procedure body, and
>the cost went way up:
>SELECT DISTINCT a.areanumber, a.areaname
>FROM areas AS a WITH (NOLOCK)
>WHERE a.regionnumber = 56 OR a.regionnumber IN (
> SELECT relatedregionnumber
> FROM regionrelationships
> WHERE regionnumber = 56)
>ORDER BY a.areaname
>cost: .518
>
>i've tweaked a bunch of stuff, and this is the best i've been able to
>do so far:
(snip)
Hi Jason,
Try this version instead:
SELECT a.areanumber, a.areaname
FROM areas AS a WITH (NOLOCK)
WHERE a.regionnumber = 56
UNION ALL
SELECT a.areanumber, a.areaname
FROM areas AS a
INNER JOIN regionrelationships AS r
ON r.relatedregionnumber = a.regionnumber
WHERE r.regionnumber = 56
AND r.relatedregionnumber <> 56
ORDER BY areaname
Indexing suggestions:
- In regionrelationships, make sure that regionnumber is the first of
the two columns in the primary key.
- In areas, have a clustered index with regionnumber as first column. Or
(maybe even better) a nonclustered index on (regionnumber, areaname,
areanumber).
Hugo Kornelis, SQL Server MVP|||thank you very much sir!

Requesting querry to view structure

Hi,
Can anybody please help me in writing the syntax to view the structure
of the table in sqlserver 2005.
For example in oracle if we want to see the table structure we will use
the command
desc table name.In similar way is there any command to view the table
structure we have created in sqlserver2005.
ThanksEXEC sp_help 'tablename';
"ganu" <girijak@.hotmail.com> wrote in message
news:1140547519.059657.128250@.g47g2000cwa.googlegroups.com...
> Hi,
> Can anybody please help me in writing the syntax to view the structure
> of the table in sqlserver 2005.
> For example in oracle if we want to see the table structure we will use
> the command
> desc table name.In similar way is there any command to view the table
> structure we have created in sqlserver2005.
>
> Thanks
>

requesting querry to insert date and time

Hi,
Can anybody telll me how to give date and time to be printed in
sqlserver programmin.For example if u want to add data to a table using
insert command how can we enter date and time in that.
please reply me soon.
thanks for helping.Did you have a look on the getdate() function ?
INSERt INTO SomeTable
( colList ... )
VALUES
(
...
GETDATE(),
...
)
HTH, Jens Suessmeyer.|||Do you mean something like GETDATE() . This returns the system time and date
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"gija" <girijak@.hotmail.com> wrote in message
news:1140035764.737565.125890@.g44g2000cwa.googlegroups.com...
> Hi,
> Can anybody telll me how to give date and time to be printed in
> sqlserver programmin.For example if u want to add data to a table using
> insert command how can we enter date and time in that.
> please reply me soon.
> thanks for helping.
>|||INSERT table(column) SELECT '2006-02-15T05:43:21';
INSERT table(column) VALUES('2006-02-15T05:43:21');
"gija" <girijak@.hotmail.com> wrote in message
news:1140035764.737565.125890@.g44g2000cwa.googlegroups.com...
> Hi,
> Can anybody telll me how to give date and time to be printed in
> sqlserver programmin.For example if u want to add data to a table using
> insert command how can we enter date and time in that.
> please reply me soon.
> thanks for helping.
>

Requesting null value

if

(paramNum1_cel.Value ==null)this.gerentes_beneficios.numCelular = // what do i put hereelsethis.gerentes_beneficios.numCelular = (int) result ["Num1_cel"];

i need something to define a resultset, this field can be null or not

thanks a lot

I don't understand your question. Could you explain it further?

Requesting it(PK) after saving data with auto Prim.Key

Hi,

I have a page for saving a "home" data. As the page unloads I willredirect the user to the page on which he can add the photos to this"home".

Well I structured my photo adding page with first requesting the "homeID" which is an auto Integer (PK).
Because it's renaming the uploaded photos with the homeID and _01, _02...

Because of that i need to send the user to the photo adding page with "response.redirect.....aspx?homeID=....

homeID= ?

But I don't know how to get the homeID value, that the SQL Server automaticly selects for the data. As it is just saving it...

Thanks a lot.If you are using Stored Procedures, make an output parameter for your new ID, like

@.NewHomeId INT OUTPUT

then right after you do your insert

do

SET @.NewHomeId = SCOPE_IDENTITY()

and the value will be returned with the parameters. on the data layer, you can do

(assuming you output parmetere is the last in the params)
int val = (int)parms[parms.length - 1].Value;

Requesting help with complicated aggregation of a table

I am moving our Databases from My SQL to SQL Server 2000 and am now starting to use views to access data. Very nice feature and worth the upgrade. I need to rewrite a report that we currently have as an Excel file that aggregates tinyint data that has p
ossible of 1-9, * (invalid) and " " (space). The way I am trying to do this in MySQL is like this:
SELECT con_id, count(q2*) (q3*) (q4*)
FROM db_data where con_id in (select con_id FROM congregs where syn_id = "1A")
GROUP BY q2, q3, q4, con_id
The following SQL Statement works with the following output but have not yet been able to get the above statement working
SELECT q3, count(*)
FROM db_data
WHERE con_id=1234567
GROUP BY q3
-- Output --
q3 count(*)
7
*1
14
238
33
I have 415 columns in the db_data table and am wondering if there is any way to write a query that does the above to the entire table (or a selection of rows "from row # to row #). To further compound the problem (and greater sweeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to the * and blank possibilities, there can be 1, 2 or 3; in q133 there is a possible 1-7 plus the * and blank.
I can not get the above query to work in SQL Server, only MySQL.
Any help would be welcomed
Thank you
mchemnitz,
Do you mean the following?
SELECT con_id, count(q2), count(q3), count (q4)
FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
GROUP BY con_id
ORDER BY con_id, q2, q3, q4
The above is COUNT(ALL col), but you might prefer COUNT (DISTINCT col).
Russell Fields
"mchemnitz" <mchemnitz@.discussions.microsoft.com> wrote in message
news:17E24782-1BC0-40C3-BCE3-EB2ED78F57D0@.microsoft.com...
> I am moving our Databases from My SQL to SQL Server 2000 and am now
starting to use views to access data. Very nice feature and worth the
upgrade. I need to rewrite a report that we currently have as an Excel file
that aggregates tinyint data that has possible of 1-9, * (invalid) and " "
(space). The way I am trying to do this in MySQL is like this:
> SELECT con_id, count(q2*) (q3*) (q4*)
> FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
> GROUP BY q2, q3, q4, con_id
> The following SQL Statement works with the following output but have not
yet been able to get the above statement working
> SELECT q3, count(*)
> FROM db_data
> WHERE con_id=1234567
> GROUP BY q3
> -- Output --
> q3 count(*)
> 7
> * 1
> 1 4
> 2 38
> 3 3
> I have 415 columns in the db_data table and am wondering if there is any
way to write a query that does the above to the entire table (or a selection
of rows "from row # to row #). To further compound the problem (and greater
sweeten a solution) the possible outputs change depending upon the question.
So in q3, in addition to the * and blank possibilities, there can be 1, 2 or
3; in q133 there is a possible 1-7 plus the * and blank.
> I can not get the above query to work in SQL Server, only MySQL.
> Any help would be welcomed
> Thank you

Requesting help with complicated aggregation of a table

I am moving our Databases from My SQL to SQL Server 2000 and am now starting
to use views to access data. Very nice feature and worth the upgrade. I n
eed to rewrite a report that we currently have as an Excel file that aggrega
tes tinyint data that has p
ossible of 1-9, * (invalid) and " " (space). The way I am trying to do this
in mysql is like this:
SELECT con_id, count(q2*) (q3*) (q4*)
FROM db_data where con_id in (select con_id FROM congregs where syn_id = "1A
")
GROUP BY q2, q3, q4, con_id
The following SQL Statement works with the following output but have not yet
been able to get the above statement working
SELECT q3, count(*)
FROM db_data
WHERE con_id=1234567
GROUP BY q3
-- Output --
q3 count(*)
7
* 1
1 4
2 38
3 3
I have 415 columns in the db_data table and am wondering if there is any way
to write a query that does the above to the entire table (or a selection of
rows "from row # to row #). To further compound the problem (and greater s
weeten a solution) the poss
ible outputs change depending upon the question. So in q3, in addition to t
he * and blank possibilities, there can be 1, 2 or 3; in q133 there is a pos
sible 1-7 plus the * and blank.
I can not get the above query to work in SQL Server, only MySQL.
Any help would be welcomed
Thank youmchemnitz,
Do you mean the following?
SELECT con_id, count(q2), count(q3), count (q4)
FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
GROUP BY con_id
ORDER BY con_id, q2, q3, q4
The above is COUNT(ALL col), but you might prefer COUNT (DISTINCT col).
Russell Fields
"mchemnitz" <mchemnitz@.discussions.microsoft.com> wrote in message
news:17E24782-1BC0-40C3-BCE3-EB2ED78F57D0@.microsoft.com...
> I am moving our Databases from My SQL to SQL Server 2000 and am now
starting to use views to access data. Very nice feature and worth the
upgrade. I need to rewrite a report that we currently have as an Excel file
that aggregates tinyint data that has possible of 1-9, * (invalid) and " "
(space). The way I am trying to do this in mysql is like this:
> SELECT con_id, count(q2*) (q3*) (q4*)
> FROM db_data where con_id in (select con_id FROM congregs where syn_id =
"1A")
> GROUP BY q2, q3, q4, con_id
> The following SQL Statement works with the following output but have not
yet been able to get the above statement working
> SELECT q3, count(*)
> FROM db_data
> WHERE con_id=1234567
> GROUP BY q3
> -- Output --
> q3 count(*)
> 7
> * 1
> 1 4
> 2 38
> 3 3
> I have 415 columns in the db_data table and am wondering if there is any
way to write a query that does the above to the entire table (or a selection
of rows "from row # to row #). To further compound the problem (and greater
sweeten a solution) the possible outputs change depending upon the question.
So in q3, in addition to the * and blank possibilities, there can be 1, 2 or
3; in q133 there is a possible 1-7 plus the * and blank.
> I can not get the above query to work in SQL Server, only MySQL.
> Any help would be welcomed
> Thank you

Requesting Advanced T-SQL help

Need some guru help with a monster T-SQL query. Trying to make it more
efficient, possibly less code, and anything else I can do to it to make it
run faster. This is an SP and Tables created by the person I replaced, but
I need to deal with it...for now.
I have a Status table that has 50 columns and currently contains 3.3+
million records in it. I don't have the luxury of altering the table, so I
am stuck with this thing (table structure attached at the end of this post).
Still being relatively new here, I wouldn't even know where to begin
changing it if I could. Anyhow...
Quick description: Our website provides a search utility that deals with
this one table, and is a typical search in that you can search by name, a
date range, member ID, etc. There is also a ListBox that displays Doctors
the user view info on, which allows multiple-selection. Making the
appropriate selections, providing any other search criteria, the search app
queries against this rather large table and returns a listing of associated
records. That's the goal.
The ugly truth...
The query that fires off from this search is equally gargantuan (SP code
posted below) and was taking for-ev-er to run. I was able to play around
with the indexes on the table (one clustered and one non-clustered) which
dropped the execution time of a single run to an average of 5 seconds. If I
tried any additional indexes, or modifying these two, it only seemed to make
the execution take longer. In order to search on multiple doctor numbers
(that pesky multi-select listbox), the .asp code runs the stored proc once
for each doctor number and then appends each set of records that comes back
and displays them in a 'list' on the webpage. But when it takes at best 4~5
seconds per sp execution, selecting 3 or 4 Doctors will have the users
frustrated beyond belief waiting for the search results.
So, I ask for the help of T-SQL Gurus here on improving this monster. I
have tried, but am not getting anywhere. Here is the code, and I hope to
get some suggestions. Thanks
-- Andrew
P.S.: I hope the below is enough as I cannot share the data in the table due
to privacy concerns.
Table Structure
CREATE TABLE [MedMC_Claim_Status] (
[TMBR#] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MMFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MMLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADOC#] [decimal](9, 0) NULL ,
[AHCCCS_ID] [decimal](9, 0) NULL ,
[DFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSSN#] [decimal](9, 0) NULL ,
[DFTAX#] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TPAYE#] [decimal](9, 0) NULL ,
[PDFNAM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDINIT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDLNAM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TCLAIM] [decimal](9, 0) NOT NULL ,
[TSRVDT] [smalldatetime] NULL ,
[TSRVTDT] [smalldatetime] NULL ,
[TSTATS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TSTATS_DESC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TFLAG4] [decimal](6, 0) NOT NULL ,
[TRCVDT] [smalldatetime] NULL ,
[TPROC] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TMOD3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TREVCD] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TQTY] [decimal](7, 0) NULL ,
[TAUTH#] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TADDDT] [smalldatetime] NULL ,
[TCHRGS] [decimal](8, 2) NULL ,
[TAUTH] [decimal](8, 2) NULL ,
[TCOPAY] [decimal](8, 2) NULL ,
[TCOB] [decimal](8, 2) NULL ,
[TFLD16] [decimal](8, 2) NULL ,
[TWHIAM] [decimal](8, 2) NULL ,
[TDIS] [decimal](8, 2) NULL ,
[TDED] [decimal](8, 2) NULL ,
[TPADDT] [smalldatetime] NULL ,
[TCHK#] [decimal](6, 0) NULL ,
[TINV#] [decimal](6, 0) NULL ,
[DENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PDENITY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HLDESC] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HFLAG2] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR1] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DADR2] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DCITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DSTAT] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DZIPC] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dtmCrea_Date] [datetime] NULL CONSTRAINT
[DF_MedMC_Claim_Status_dtmCrea_Date] DEFAULT (getdate())
) ON [PRIMARY]
GO
Table Indexes
CREATE CLUSTERED INDEX [CL_MedMCClaimStatus_TmbrNum,TDocNum,TCl
aim,TFlag4]
ON [dbo].[MedMC_Claim_Status]([TMBR#], [TADOC#], [TCLAIM], [TFLAG4]) ON
[PRIMARY]
CREATE INDEX [NCL_MedMCClaimStatus_MMFnam,MMLnam,AHCC
CSID,DFTaxNum] ON
[dbo].[MedMC_Claim_Status]([MMFNAM], [MMLNAM], [AHCCCS_ID], [DFTAX#]) ON
[PRIMARY]
GO
Search Query
CREATE PROCEDURE stp_MedMC_Claim_1st_Level_Retrieve
@.TADOC# DECIMAL(9,0) = NULL,
@.DFTAX# char(12) = NULL,
@.TSRVDTfrom datetime = NULL, --'1/1/2005'
@.TSRVDTto datetime = NULL,
@.FINAL_STATUS VARCHAR(20) = null,
@.TMBR# VARCHAR(12) = NULL,
@.MMFNAM VARCHAR(12) = NULL,
@.MMLNAM VARCHAR(12) = NULL,
@.Loc decimal(9,0) = null
AS
set nocount on
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
C.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ', ' .
', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P' THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' +
RTRIM(LTRIM(A.DINIT)) + '. ', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> '' THEN
REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) + '. ', ' . ',
' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
'RECEIVED'
when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM, MAX(TFLAG4) AS TFLAG4
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) E
INNER JOIN
(
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
INNER JOIN
(
select TMBR#, TCLAIM, COUNT(*) AS TOTALCOUNT
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) C
INNER JOIN
(
select
TMBR#, TCLAIM,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) D
ON C.TMBR# = D.TMBR# AND C.TCLAIM = D.TCLAIM
ON B.TMBR# = C.TMBR# and B.TCLAIM = C.TCLAIM
ON E.TMBR# = B.TMBR# and E.TCLAIM = B.TCLAIM
ON A.TMBR# = E.TMBR# AND A.TCLAIM = E.TCLAIM AND A.TFLAG4 = E.TFLAG4
where 1 = 1
AND A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and coalesce(@.TSRVDTto,
@.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where 1 = 1
AND FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
set nocount offOn Thu, 11 May 2006 15:41:13 -0700, Andrew wrote:

>Need some guru help with a monster T-SQL query. Trying to make it more
>efficient, possibly less code, and anything else I can do to it to make it
>run faster. This is an SP and Tables created by the person I replaced, but
>I need to deal with it...for now.
>I have a Status table that has 50 columns and currently contains 3.3+
>million records in it. I don't have the luxury of altering the table, so I
>am stuck with this thing (table structure attached at the end of this post)
.
>Still being relatively new here, I wouldn't even know where to begin
>changing it if I could. Anyhow...
Hi Andrew,
First some quick comments on the table. Keep them until you DO have time
to improve the schema.
* FIND AND DECLARE THE KEY!!!!!! You curently have two nonunique
indexes, but no PRIMARY KEY constraint, no UNIQUE constraint and no
UNIQUE indexes. This is an open invitation to duplicated data. It also
deprives the query optimizer of essential knowledge about your data that
it can use to find more efficient execution plans. And it limits my
possibilities to modify your query.
* Most of the columns allow NULLs. Allowing NULLs on such a high
fraction of the columns is quite unlikely. Change as many of them to NOT
NULL as you can.
* Consider changing the decimal (x, 0) columns to int.
* Change the varchar(1) columns to char(1), unless you really need to
store the difference between '' (empty string) and ' ' (one space). Each
varchar column incurs two bytes of overhead to store the length. No
problem for longer varchar columns (you'll save more than two bytes on
average), but a waste for short strings.

>Quick description: Our website provides a search utility that deals with
>this one table, and is a typical search in that you can search by name, a
>date range, member ID, etc.
Read Erlands article: http://www.sommarskog.se/dyn-search.html. There
are many ways to improve performance of flexible search queries such as
yours. In the rest of my reply, I won't address this part of your query
since Erland already covered that very well.
One thing I will mention because it can really make a HUGE difference:
do you really need a LIKE search in all these cases? Searching for
equality is often lots faster. A LIKE search for a pattern that starts
with % or _ (or a pattern unknown to the optimizer, e.g. because of a
variable) almost always results in table or index scans.
(snip)
>The query that fires off from this search is equally gargantuan (SP code
>posted below) and was taking for-ev-er to run. I was able to play around
>with the indexes on the table (one clustered and one non-clustered) which
>dropped the execution time of a single run to an average of 5 seconds.
Congratulations - that's a great achievement! But no reason to stop
there.

> If I
>tried any additional indexes, or modifying these two, it only seemed to mak
e
>the execution take longer.
Then you have probably found the best set of indexes for this query. I
haven't looked at the indexes and their usability; I concentrated on
rewriting the query itself. Maybe you should fiddle the indexes some
more after testing my version of the query (below).

> In order to search on multiple doctor numbers
>(that pesky multi-select listbox), the .asp code runs the stored proc once
>for each doctor number and then appends each set of records that comes back
>and displays them in a 'list' on the webpage. But when it takes at best 4~
5
>seconds per sp execution, selecting 3 or 4 Doctors will have the users
>frustrated beyond belief waiting for the search results.
Indeed. Luckily, Erland has covered this common problem as well. Check
out this article: http://www.sommarskog.se/arrays-in-sql.html. I suggest
that you use one of the techniques to get the selected doctor numbers in
a table, then join against that table. This is another aspect of your
question that I won't cover any further.

>So, I ask for the help of T-SQL Gurus here on improving this monster. I
>have tried, but am not getting anywhere. Here is the code, and I hope to
>get some suggestions. Thanks
(snip H U G E query)
The first thing that struck me is that you have a totall of four derived
tables that all use the EXACT same FROM and WHERE clause (unless I am
overlooking some minor difference?). It looks as if your predecessor
thought that you were limited to one aggregate per subquery. You can
replace these four subqueries with one. That also rids you of the (in
this case) unneeded non-standard ordering of JOIN and ON clauses. Was
your predecessor coding for job security?
The main query also repeats the exact same WHERE clause (apart from the
totally unneeded 1 = 1), but it lacks the GROUP BY that is present in
the derived tables. I can't say that I like this - you might end up with
several detail rows that each show the same aggregated results in
addition to the different details. I guess "AND A.TFLAG4 = E.TFLAG4" is
intended to solve this, but I'm not sure if this is failsafe. This is
where I miss knowing the keys!
Another suspicious part is the CASE statement for the status:

> case
> when D.S1 > 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 = 0 then
>'RECEIVED'
> when D.S1 = 0 AND D.S2 > 0 then 'PROCESSING'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 > 0 then 'PAID'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 > 0 then 'DENIED'
> when D.S1 = 0 AND D.S2 = 0 AND D.S3 = 0 AND D.S4 = 0 AND D.S5 > 0 then
>'VOID'
> END AS FINAL_STATUS
Are you aware that many combinations of S1, S2, ..., and S5 will fall
through all the WHEN clauses and yield a NULL result? (For instance if
both S1 and S2 are >0, or when all are =0, to name just two examples). I
don't know your data, so you'll have to decide if this is a problem. A
possible rewrite (depending on your data -- don't copy blindly, it is
not the exact same logic!!) that takes advantage of the order of
evaluation of the CASE expression would be:
CASE
WHEN D.S1 > 0 THEN 'Received'
WHEN D.S2 > 0 THEN 'Processing'
WHEN D.S3 > 0 THEN 'Paid'
WHEN D.S4 > 0 THEN 'Denied'
ELSE 'Void'
END
But in the rewritten query bellow, I'll retain the original.
The only "safe" improvement of your query that I see is the removal of
the duplicated derived tables. I expect a good performance improvement
from that. I'm quite sure that there are more possibilities to optimize
the query even further, but they require a better understanding of the
data than I have (at least knowledge of the keys!).
Here's my suggestion for a rewritten query:
SELECT * FROM
(
select
A.TMBR#,
A.TCLAIM,
A.TFLAG4,
B.TOTALCOUNT,
REPLACE(RTRIM(LTRIM(A.MMFNAM)) + ' ' + RTRIM(LTRIM(A.MMINIT)) + '. ',
' . ', ' ') + RTRIM(LTRIM(A.MMLNAM)) AS MMNAME,
A.TADOC#,
A.DFTAX# ,
A.AHCCCS_ID,
A.DADR1,
CASE
WHEN A.DENITY = 'P'
THEN REPLACE(RTRIM(LTRIM(A.DFNAM)) + ' ' + RTRIM(LTRIM(A.DINIT)) + '.
', ' . ', ' ') + RTRIM(LTRIM(A.DLNAM))
ELSE RTRIM(LTRIM(A.DLNAM)) + RTRIM(LTRIM(A.DFNAM))
END AS DNAME,
A.TPAYE#,
CASE
WHEN A.PDENITY = 'P' AND RTRIM(LTRIM(A.PDINIT)) <> ''
THEN REPLACE(RTRIM(LTRIM(A.PDFNAM)) + ' ' + RTRIM(LTRIM(A.PDINIT)) +
'. ', ' . ', ' ') + RTRIM(LTRIM(A.PDLNAM))
ELSE RTRIM(LTRIM(A.PDLNAM)) + RTRIM(LTRIM(A.PDFNAM))
END AS PDNAME,
CONVERT(varchar(10), A.TSRVDT, 101) as TSRVDT,
A.TSTATS,
A.TSTATS_DESC,
B.TTFLD16,
case
when B.S1 > 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 = 0
then 'RECEIVED'
when B.S1 = 0 AND B.S2 > 0 then 'PROCESSING'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 > 0 then 'PAID'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 > 0 then 'DENIED'
when B.S1 = 0 AND B.S2 = 0 AND B.S3 = 0 AND B.S4 = 0 AND B.S5 > 0
then 'VOID'
END AS FINAL_STATUS
from MedMC.dbo.MedMC_Claim_Status A
INNER JOIN
(
select TMBR#, TCLAIM,
MAX(TFLAG4) AS TFLAG4,
SUM(TFLD16) AS TTFLD16,
COUNT(*) AS TOTALCOUNT,
SUM(CASE when TSTATS = '1' then 1 else 0 END) AS S1,
SUM(CASE when TSTATS = '2' then 1 else 0 END) AS S2,
SUM(CASE when TSTATS = '3' then 1 else 0 END) AS S3,
SUM(CASE when TSTATS = '4' then 1 else 0 END) AS S4,
SUM(CASE when TSTATS = '5' then 1 else 0 END) AS S5
from MedMC.dbo.MedMC_Claim_Status
WHERE AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND DFTAX# = COALESCE(@.DFTAX#, DFTAX#)
and TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND TMBR# = COALESCE(@.TMBR#, TMBR#)
AND MMFNAM LIKE COALESCE(@.MMFNAM, MMFNAM)
AND MMLNAM LIKE COALESCE(@.MMLNAM, MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
GROUP BY TMBR#, TCLAIM
) B
ON A.TMBR# = B.TMBR# AND A.TCLAIM = B.TCLAIM AND A.TFLAG4 = B.TFLAG4
WHERE
A.AHCCCS_ID = COALESCE(@.TADOC#, AHCCCS_ID)
AND A.DFTAX# = COALESCE(@.DFTAX#, A.DFTAX#)
AND TSRVDT between coalesce(@.TSRVDTfrom, TSRVDT) and
coalesce(@.TSRVDTto, @.TSRVDTfrom, TSRVDT)
AND A.MMFNAM LIKE COALESCE(@.MMFNAM, A.MMFNAM)
AND A.MMLNAM LIKE COALESCE(@.MMLNAM, A.MMLNAM)
and ltrim(rtrim(tadoc#)) = coalesce(@.Loc,tadoc#)
) T
where FINAL_STATUS = COALESCE(UPPER(@.FINAL_STATUS), FINAL_STATUS)
ORDER BY TSRVDT desc
(Untested, since you didn't post INSERT statements with sample data to
test on and expected results to verify my results).
Hugo Kornelis, SQL Server MVP|||In addition to what Hugo posted, I grabbed the query execution plan and
noticed a lot of table scans and sort operations. If you can figure out a
way to tighten up the WHERE clauses and the indexing a little more to match
these queries, you can probably improve performance dramatically. All those
LTRIM(RTRIM()) and REPLACE() statements in the WHERE clause don't help,
since it has to perform those operations on every single row. The RTRIM's
are superfluous, and why not LTRIM() it at insert/update time?
Take a look at the query execution plan for the best ideas on how to
optimize.
You probably already know it's a bad idea to use a # character in
variable/parameter/column names - I guess the guy you replaced wasn't aware
:)
"Andrew" <AndrewR2k1@.hotmail.com> wrote in message
news:unyFEvUdGHA.5048@.TK2MSFTNGP04.phx.gbl...
> Need some guru help with a monster T-SQL query. Trying to make it more
> efficient, possibly less code, and anything else I can do to it to make it
...|||Another option to explore (after you look at Hugo's and Mike's) is the
issue of your clustered index. You didn't say how often data is
inserted into the primary table, or what your maintenance plan is , but
I'd be willing to bet that with such a wide clustered index, you are
looking at some heavy duty fragmentation. You might try moving your
clustered index to the last column in the table (the one that defaults
to GETDATE()) to see if that improves response; rebuild the current
clustered index as a nonclustered index on those same columns.
Of course, if you rarely insert data, then disregard my advice;
instead, focus on a good defragmentation plan that runs often enough to
keep your indexes in shape.
Stu

Requesting a attribute + registering info when availible

I'm working with VB.NET. I have three questions an i hope somone will answer.
I want to reguest this pice ## IC25N040ATMR04-0 ## How can i do that?
<Hard_disks>
<primary_disk size="40007761920"
sernum="MRG257K2EBGTUH">IC25N040ATMR04-0</primary_disk>
<disk name="C:" sys="Ntfs" size="10.0 Gb" freePr="23.5" free="2.35 Gb" />
<disk name="D:" sys="Ntfs" size="28.1 Gb" freePr="32.7" free="9.21 Gb" />
</Hard_disks>
I have tried to do it like this just like otherones i have don but this one
is different. I don't know how to do it?!!!
'-- Harddisk Informatie
node = XMLDocument.SelectSingleNode("report/Hard_disks")
AttrNode = node.Attributes.GetNamedItem("primary_disk")
strGrootte = AttrNode.Value
MsgBox(strSerienummer)
Exit Sub
i get a debug massage that says:
An unhandled exception of type 'System.NullReferenceException' occurred in
WindowsApplication5.exe
Additional information: De objectverwijzing is niet op een exemplaar van een
object ingesteld.
what should i do ? can you show me?
Second Question: image you have this as a XML file:
<Hard_disks>
<primary_disk size="30733516800" sernum="N40W3BRC">Maxtor
33073H4</primary_disk>
<disk name="C:" sys="Ntfs" size="5.00 Gb" freePr="47.6" free="2.38 Gb" />
<disk name="D:" sys="Ntfs" size="12.0 Gb" freePr="26.7" free="3.20 Gb" />
<disk name="E:" sys="Fat32" size="12.3 Gb" freePr="3.3" free="408.9 Mb" />
<secondary_disk size="1083801600" sernum="WD-WM3531743957">WDC
AC21000H</secondary_disk>
<disk name="F:" sys="Fat" size="1.03 Gb" freePr="11.3" free="116.4 Mb" />
</Hard_disks>
How can i make it so that when there is a second HD that it will registere
it and when is not there it don't? Somebody a clue
nodeList = XMLDocument.SelectNodes("/report/ juiste info /juiste info")
For i = 0 To nodeList.Count - 1
node = nodeList.Item(i)
Third Question is:
When i have more than one table where i want to insert information, what is
the correct way to do it. I can insert one table with more values but i can't
insert more table i get a syntaxis wrong!!
this is my SQL line:
strSQL = "INSERT INTO software, operatings (naam, versie, grootte,
datum),(os) VALUES ('" & strNaam & "','" & strVersie & "','" & strGrootte &
"','" & StrDatum & "'),('" & strNaam & "',)"
Beginner Programmer!
Your best bet would be to ask this question in the VB.Net newsgroups.
This newsgroup is for SQL Server XML questions.
Bryant