May 8, 2008 at 1:25 pm
SELECT DISTINCT
b.boxid,
b.boxid,
b.dispenserid,
b.boxqty,
b.formqty,
b.datestamp,
b.[timestamp],
b.linenum,
b.operator,
b.shipperid,
b.wholesalerid,
0.00 as shipcost,
b.crxflag,
b.actualqty,
b.status,
b.complaint,
b.condition,
b.weight,
b.exception,
b.receipt,
b.creditqty,
b.perc_notified,
b.perc_invoiced,
b.is_collect,
b.waybill,
b.clin_trial,
b.totalboxes,
b.OpenBy
FROM
USFPIMS..box b
INNER JOIN USFPIMS..dispenser d on b.dispenserid = d.dispenserid
INNER JOIN USFPIMS..scan s on b.boxid = s.boxid
WHERE
(s.credit IS NULL AND b.status = 'OUT' AND b.boxid NOT IN (select boxid from USFPIMS..box) )
AND
((s.client = 'GSK' AND d.province IN ('QC','SK') and d.sdm_store = 'Y')
OR
(s.client = 'GSK' AND d.province IN ('QC','SK','PE','NS','NB','NF') AND (LEFT(d.store_num,3) IN ('ZEL','HBC')))
OR
((s.client = 'MCP' OR s.client = 'JNJ') AND d.sdm_store = 'Y')
OR
(s.client = 'PCP' OR s.client = 'PCR' OR s.client = 'HOS' OR s.client = 'NRC' OR s.client = 'RPC' OR s.client = 'JOR' )
OR
((s.client IN (select clientcode from manu_clients)) AND (LEFT(d.store_num,3) NOT IN ('ZEL','HBC') AND d.sdm_store != 'Y') ))
Any suggestions how to re-write this query
in a better way?
Thanks
May 8, 2008 at 2:17 pm
Isn't this
(s.credit IS NULL AND b.status = 'OUT' AND b.boxid NOT IN (select boxid from USFPIMS..box)
AND
((s.client = 'GSK' AND d.province IN ('QC','SK') and d.sdm_store = 'Y')
OR
(s.client = 'GSK' AND d.province IN ('QC','SK','PE','NS','NB','NF') AND
(LEFT(d.store_num,3) IN ('ZEL','HBC')))
always false? You're getting b.boxid from usfpims..box that should not exist in this table.
so this inner AND is false, and as it is first operand of outer AND, the whole expression seems to be false.
Am I right?
...and your only reply is slàinte mhath
May 8, 2008 at 2:33 pm
- can you provide DDL for the objects (including indexes) ?
- many or-s, many functions for filtering (leftx,3) in (...)
may discourage index usage !
Post the execution plan (xml plan !) as an attachment to your reply.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 8, 2008 at 2:37 pm
My first question is: What do you mean by "better"? Faster? More standard? Easier to read? Gets the right results (assuming current version doesn't)?
As already pointed out, it looks to me like the first condition-set will always return false.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 9, 2008 at 6:41 am
Pyotr,
You are absolutely right!
This stupid query will always return no records
.....
FROM
USFPIMS..box b
INNER JOIN USFPIMS..dispenser d on b.dispenserid = d.dispenserid
INNER JOIN USFPIMS..scan s on b.boxid = s.boxid
WHERE
(s.credit IS NULL AND b.status = 'OUT' AND b.boxid NOT IN (SELECT boxid FROM USFPIMS..box))
AND ......
because of "b.boxid NOT IN (SELECT boxid FROM USFPIMS..box)" portion.
I knew something is wrong with this query.
No wonder they have problems with USFRET database
where they insert this data.
May 9, 2008 at 7:32 am
Sorry guys.
I posted the wrong query.
I extracted it from DTS and wasn't realizing that
it was transformation from UFPIMS to USFRET.
Here is the correct code:
select distinct b.boxid,b.boxid,b.dispenserid,b.boxqty,b.formqty,b.datestamp,b.[timestamp],b.linenum,b.operator,b.shipperid,
b.wholesalerid,0.00 as shipcost,b.crxflag,b.actualqty,b.status,b.complaint,b.condition,b.weight,b.exception,b.receipt,
b.creditqty,b.perc_notified,b.perc_invoiced,b.is_collect,b.waybill,b.clin_trial,b.totalboxes,b.OpenBy
from box b
inner join dispenser d on b.dispenserid = d.dispenserid
inner join scan s on b.boxid = s.boxid
where (s.credit is null and b.status = 'OUT' and b.boxid not in (select boxid from usfpims.dbo.box) ) and
((s.client = 'GSK' and d.province in ('QC','SK') and d.sdm_store = 'Y')
or
(s.client = 'GSK' and d.province in ('QC','SK','PE','NS','NB','NF') and (left(d.store_num,3) in ('ZEL','HBC')))
or
((s.client = 'MCP' or s.client = 'JNJ') and d.sdm_store = 'Y')
or
(s.client = 'PCP' or s.client = 'PCR' or s.client = 'HOS' or s.client = 'NRC' or s.client = 'RPC' or s.client = 'JOR' )
or
((s.client in (select clientcode from manu_clients)) and (left(d.store_num,3) not in ('ZEL','HBC') and d.sdm_store != 'Y') ))
May 9, 2008 at 8:29 am
This is the best I could do.
It's still hard to read and understand.
Can somebody make it more readable and easier to follow?
SELECT DISTINCT
b.boxid,
b.boxid,
b.dispenserid,
b.boxqty,
b.formqty,
b.datestamp,
b.[timestamp],
b.linenum,
b.operator,
b.shipperid,
b.wholesalerid,
0.00 as shipcost,
b.crxflag,
b.actualqty,
b.status,
b.complaint,
b.condition,
b.weight,
b.exception,
b.receipt,
b.creditqty,
b.perc_notified,
b.perc_invoiced,
b.is_collect,
b.waybill,
b.clin_trial,
b.totalboxes,
b.OpenBy
FROM
USFRET..box b
INNER JOIN USFRET..dispenser d ON b.dispenserid = d.dispenserid
INNER JOIN USFRET..scan s ON b.boxid = s.boxid
INNER JOIN USFRET..manu_clients mc ON s.client = mc.clientcode
WHERE
(
s.credit IS NULL AND b.status = 'OUT' AND b.boxid NOT IN (select boxid from USFPIMS..box)
)
AND
(
(s.client = 'GSK' AND d.province IN ('QC','SK') and d.sdm_store = 'Y')
OR
(s.client = 'GSK' AND d.province IN ('QC','SK','PE','NS','NB','NF') AND LEFT(d.store_num,3) IN ('ZEL','HBC')
)
OR
((s.client = 'MCP' OR s.client = 'JNJ') and (d.sdm_store = 'Y'))
OR
(s.client = 'PCP' OR s.client = 'PCR' or s.client = 'HOS' OR s.client = 'NRC' OR s.client = 'RPC' OR s.client = 'JOR')
OR
LEFT(d.store_num,3) NOT IN ('ZEL','HBC') AND d.sdm_store <> 'Y'
)
May 9, 2008 at 8:51 am
Shema and indexes:
USE [USFRET]
GO
/****** Object: Table [dbo].[box] Script Date: 05/09/2008 10:34:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[box](
[boxid] [char](10) NOT NULL,
[formid] [char](10) NULL,
[dispenserid] [char](7) NULL,
[boxqty] [smallint] NULL,
[formqty] [smallint] NULL,
[datestamp] [datetime] NULL,
[timestamp] [datetime] NULL,
[linenum] [smallint] NULL,
[operator] [char](12) NULL,
[shipperid] [char](3) NULL,
[wholesalerid] [char](7) NULL,
[shipcost] [decimal](14, 2) NULL,
[crxflag] [char](1) NULL,
[actualqty] [smallint] NULL,
[status] [char](3) NULL,
[complaint] [char](1) NULL,
[condition] [char](3) NULL,
[weight] [decimal](6, 2) NULL,
[exception] [smallint] NULL,
[receipt] [datetime] NULL,
[creditqty] [smallint] NULL,
[perc_notified] [decimal](5, 2) NULL,
[perc_invoiced] [decimal](5, 2) NULL,
[is_collect] [char](1) NULL,
[waybill] [char](35) NULL,
[clin_trial] [char](1) NULL,
[totalboxes] [int] NULL,
[OpenBy] [char](25) NULL,
CONSTRAINT [PK__box__1DB06A4F] PRIMARY KEY NONCLUSTERED
(
[boxid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--table box indexes
--------------------
datestamp,perc_notified
waybill
dispenserid
wholesalerid
receipt
exception
boxid (PK)
USE [USFRET]
GO
/****** Object: Table [dbo].[dispenser] Script Date: 05/09/2008 10:44:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[dispenser](
[dispenserid] [char](7) NOT NULL,
[cpha_num] [char](20) NULL,
[name] [char](40) NULL,
[address] [char](40) NULL,
[address_2] [char](40) NULL,
[city] [char](20) NULL,
[province] [char](10) NULL,
[postalcode] [char](7) NULL,
[fax_no] [char](13) NULL,
[telephone] [char](13) NULL,
[hospital] [char](1) NULL,
[wholesaler] [char](1) NULL,
[overlay_dt] [datetime] NULL,
[operator] [char](12) NULL,
[sdm_store] [char](1) NULL,
[store_num] [char](20) NULL,
[char](60) NULL,
[contact] [char](35) NULL,
[comments] [char](200) NULL,
[CPS] [char](1) NULL,
[company_rep] [char](1) NULL,
[JOI] [char](1) NULL,
[NotValid] [char](1) NULL,
[ProcessFlag] [char](1) NULL,
[DefReason] [char](3) NULL,
[Supervisor] [char](1) NULL,
[PJC] [char](1) NULL,
CONSTRAINT [PK__dispenser__2704CA5F] PRIMARY KEY NONCLUSTERED
(
[dispenserid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--table dispenser indexes
-------------------------
wholesaler non_clustered,non_unique
postalcode non_clustered,non_unique
telephone non_clustered,non_unique
name clustered
dispenserid (PK)
USE [USFRET]
GO
/****** Object: Table [dbo].[scan] Script Date: 05/09/2008 10:47:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[scan](
[licplate] [char](10) NOT NULL,
[boxid] [char](10) NULL,
[din] [char](8) NULL,
[upc] [char](20) NULL,
[pkgsize] [int] NULL,
[client] [char](3) NULL,
[lotnumber] [char](15) NULL,
[expiration] [datetime] NULL,
[quantity] [int] NULL,
[fullness] [decimal](4, 2) NULL,
[reason] [char](3) NULL,
[casequantity] [smallint] NULL,
[crxnarc] [char](1) NULL,
[cage] [char](1) NULL,
[authcode] [char](20) NULL,
[sortid] [char](10) NULL,
[handling] [char](3) NULL,
[exception] [smallint] NULL,
[dest] [char](3) NULL,
[recallnum] [int] NULL,
[disposition] [char](3) NULL,
[status] [char](3) NULL,
[value] [decimal](14, 2) NULL,
[valuepct] [smallint] NULL,
[process_line] [smallint] NULL,
[process_operator] [char](12) NULL,
[process_date] [datetime] NULL,
[process_time] [datetime] NULL,
[sort_line] [smallint] NULL,
[sort_operator] [char](12) NULL,
[sort_date] [datetime] NULL,
[sort_time] [datetime] NULL,
[credit] [datetime] NULL,
[invoicenum] [int] NULL,
[invoicedate] [datetime] NULL,
[edi_notified] [char](1) NULL,
[is_invoiced] [char](1) NULL,
[bulksort] [char](1) NULL,
PRIMARY KEY NONCLUSTERED
(
[licplate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--table scan indexes
-------------------------
boxidnon_clustered,non_unique
licplatePK)
boxid/clientnon_clustered,non_unique
sortidnon_clustered,non_unique
creditnon_clustered,non_unique
process_datenon_clustered,non_unique
dinclustered
recallnumnon_clustered,non_unique
USE [USFRET]
GO
/****** Object: Table [dbo].[manu_clients] Script Date: 05/09/2008 10:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[manu_clients](
[clientcode] [char](3) NOT NULL,
[lastupdate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--no indexes on [manu_clients]
May 10, 2008 at 12:26 am
What is the actually the problem with the query? Is it too slow? Post the execution plan for your query. You can post the xml version or you can run SET SHOWPLAN_TEXT to gather the execution plan.
You have a lot going on in your where clause and the optimizer might not be able to determine the best plan for this query. Meaning you are getting a scan instead of a seek. Also, you may want to rebuild your indexes and update your statistics to make sure the optimizer is using the most current information.
May 11, 2008 at 6:00 am
The change made are listed below and recommend replacing hard-coded database names with synomyms to have location independent of the name.
Changes:
For clarity, the table alias that are one character have been replaced with a longer name.
As the columns returned are only from the box table:
1. Replaced the inner join with a exists correlated sub-query
2. Removed the DISTINCT
For the staments in the format of "column = 'a' or column = 'b' or ..." , replaced with "column in ( 'a', 'b', .. )"
For "Scan.client IN (SELECT clientcode from manu_clients)", replaced with " EXISTS (SELECT 1 from manu_clients where manu_clients.clientcode = Scan.client). Note that as SQL Server does not support "IN" with 2 columns, an exists must be used, so for consistency, recommend using "exists" for all cases. i.e.
Not supported is "where ( col1, col2) in (select col1, col2 from some_table)"
Format has been revised to align opening and closing parantheses.
SELECT Box.*
FROMdbo.Box as Box
WHEREBox.status = 'OUT'
ANDEXISTS
(SELECT 1
FROMdbo.Dispenseras Dispenser
JOIN dbo.Scanas Scan
on Scan.boxid = Box.boxid
WHERE Dispenser.Dispenserid= Box.Dispenserid
ANDScan.credit IS NULL
ANDNOT EXISTS
(select 1
fromusfpims.dbo.box
whereusfpims.dbo.box.boxid = box.boxid
)
AND((Scan.client = 'GSK'
AND Dispenser.province IN ('QC','SK')
AND Dispenser.sdm_store = 'Y'
)
OR(Scan.client = 'GSK'
AND Dispenser.province IN ('QC','SK','PE','NS','NB','NF')
AND LEFT(Dispenser.store_num,3) IN ('ZEL','HBC')
)
OR (Scan.client IN ( 'MCP', 'JNJ')
AND Dispenser.sdm_store = 'Y'
)
OR(Scan.client IN ('PCP','PCR','HOS','NRC','RPC','JOR')
)
OR(EXISTS
(SELECT 1
frommanu_clients
wheremanu_clients.clientcode = Scan.client
)
AND (LEFT(Dispenser.store_num,3) NOT IN ('ZEL','HBC')
AND Dispenser.sdm_store != 'Y')
)
)
)
SQL = Scarcely Qualifies as a Language
May 11, 2008 at 7:04 am
What are synonyms and how you create them?
May 11, 2008 at 1:36 pm
riga1966 (5/11/2008)
What are synonyms and how you create them?
You may be better off taking this question to a new thread.
Check BOL for "Understanding Synonyms " (Local BOL url) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6210e1d5-075f-47e4-ac8d-f84bcf26fbc0.htm and "using synonymns" ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c3d3f14f-4cc1-465c-89b6-0299f7e77bc0.htm
Check BOL for "create synonym"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2008 at 10:21 am
Carl,
I executed your version
but it hangs. It is executed for more than 1.5 min
and I cancel the query.
The original query returns results in 5 sec.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply