July 24, 2008 at 5:06 am
Hi,
I want to confirm for following query that its optimizted query
or not?
SELECT isnull(TaxStructureID,0) as TaxStructureID FROM StateDet WHERE (StateID IN( SELECT ISNULL(State.StateID, 0) AS StateID FROM State RIGHT OUTER JOIN District ON State.StateID = District.StateID RIGHT OUTER JOIN City ON District.DistrictID = City.DistrictID RIGHT OUTER JOIN LOCATION ON City.CityID = LOCATION.CITYID WHERE (LOCATION.LOCATIONID = 18))) AND (ToStateID IN( SELECT ISNULL(State.StateID, 0) AS StateID FROM State RIGHT OUTER JOIN District ON State.StateID = District.StateID RIGHT OUTER JOIN City ON District.DistrictID = City.DistrictID RIGHT OUTER JOIN LOCATION ON City.CityID = LOCATION.CITYID ) ) AND (ItemTypeID IN ( SELECT DISTINCT ITEMTYPEID FROM QuotationDET WHERE (QuotationID = 1) ) )
Regards
Sunil Kumar
July 24, 2008 at 5:17 am
It's a little hard to read formatted like that
Are all of the ISNULLs necessary? Without the table def I can't say for sure, but some of the columns wrapped in ISNULL look like they could be the priimary key columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2008 at 5:26 am
Table definition is like that
CREATE TABLE [dbo].[StateDet](
[StateID] [int] NOT NULL,
[StateDetID] [int] IDENTITY(1,1) NOT NULL,
[ItemTypeID] [int] NULL,
[ToStateID] [int] NULL,
[TaxStructureID] [int] NULL,
CONSTRAINT [PK_StateDet] PRIMARY KEY CLUSTERED
(
[StateDetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply