June 22, 2010 at 3:41 am
Dont take this the wrong way but from here it is very difficult to judge a persons skill level.
Are you aware of what an index is and how and why that aids query performance ?
June 22, 2010 at 3:49 am
ya.. we can create unique index on StartIP and EndIP.
But this will not solve my problem.
I planned to save ID in organization table in CompanyVistedProfile table when visitor visiting to any comapny. But I said earlier, data in organization table will change on regular basis so it wont be the good idea to go with the ID of table.
June 22, 2010 at 4:07 am
The first thing that you need to do is to fix the data types.
[startIP] [nvarchar](100) NULL,
[endIP] [nvarchar](100) NULL,
...
[IPAddress] [varchar](20) NULL,
..
MaskedIPAddress [bigint]
These columns need the same data types. Pick one data type and alter the other columns so that the data types match. With them different, there will be implicit conversions and indexes won't be used properly.
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
June 22, 2010 at 4:07 am
niteen2009 (6/22/2010)
ya.. we can create unique index on StartIP and EndIP.But this will not solve my problem.
Why not? Have you tested the index and has it not improved performance?
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
June 22, 2010 at 4:15 am
Thanks..
i am trying with all the changes that u have told me..
Will update you soon..
Is there any other way to join between these to tables as that joins incurs much cost.
and where we can use where condintion ?
Thank you very much..
June 22, 2010 at 4:17 am
niteen2009 (6/22/2010)
Is there any other way to join between these to tables as that joins incurs much cost.
Joins between properly indexed columns of the same data type are not very expensive.
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
June 22, 2010 at 4:27 am
I changed data type to bigint
and applied unique index.
i have attached here new execution plan here.
Thanks
June 22, 2010 at 4:35 am
Can you post the actual execution plan please, rather than the estimated plan
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
June 22, 2010 at 4:42 am
HI,
My sql query is still executing so that not able to get actual execution plan.
It was running from past 15 min. and able to fetch near about 600 rows only.
June 22, 2010 at 6:41 am
I think the following will give much better performance:
-- Create an index to speed up lookup by ip
CREATE INDEX IX1 ON [IPLocationDB_Organization](startIP) INCLUDE(endIP, organization)
-- Execute reformulated query
SELECT
CompanyVistedProfile.VisitedDate,
ISNULL(
(SELECT organization
FROM
(SELECT TOP 1 endIP, organization
FROM IPLocationDB_Organization
WHERE startIP <= MaskedIPAddress
ORDER BY startIP DESC) dt
WHERE endIP >= MaskedIPAddress)
,
'Not Recognised') AS Organization,
CompanyBasicInformation.CompanyName,
CompanyVistedProfile.IPAddress
FROM CompanyVistedProfile
LEFT OUTER JOIN CompanyBasicInformation
ON CompanyVistedProfile.CompanyId = CompanyBasicInformation.ID
The problem with the original join is that the SQL server will have to scan about half the IPLocationDB_Organization table for each row in the CompanyVisitedProfile.
My new query will use the index to immeditely find the correct row without any scanning.
If you have 17.000 rows in your IPLocationDB_Organization table, my optimized query can be several thousand times faster.
Note that it is still very important that MaskedIPAddress, startIP and endIP are the same datatype.
/SG
June 22, 2010 at 7:29 am
Thank you very very much.
June 23, 2010 at 1:39 am
Hi Experts,
Following is the query as per my old view .
SELECT DISTINCT tbl_Visitor.VisitedDate, tbl_Visitor.Organization, tbl_Visitor.ISP, tbl_Visitor.LocationID, tbl_Visitor.CompanyName, tbl_Visitor.IPAddress,
tbl_Visitor.CompanyId, R.regionName, L.country
FROM IPLocationDB_Region AS R RIGHT OUTER JOIN
IPLocationDB_Location AS L ON R.regionCode = L.region AND R.countryCode = L.country RIGHT OUTER JOIN
(SELECT CompanyVistedProfile.VisitedDate, ISNULL
((SELECT organization
FROM (SELECT TOP (1) endIP, organization
FROM IPLocationDB_Organization
WHERE (startIP <= CompanyVistedProfile.MaskedIPAddress)
ORDER BY startIP DESC) AS dtOrg
WHERE (endIP >= CompanyVistedProfile.MaskedIPAddress)), 'Not Recognised') AS Organization, ISNULL
((SELECT isp
FROM (SELECT TOP (1) endIP, isp
FROM IPLocationDB_ISP
WHERE (startIP <= CompanyVistedProfile.MaskedIPAddress)
ORDER BY startIP DESC) AS dtIsp
WHERE (endIP >= CompanyVistedProfile.MaskedIPAddress)), '') AS ISP, ISNULL
((SELECT locationID
FROM (SELECT TOP (1) endIP, locationID
FROM IPLocationDB_IP
WHERE (startIP <= CompanyVistedProfile.MaskedIPAddress)
ORDER BY startIP DESC) AS dtLoc
WHERE (endIP >= CompanyVistedProfile.MaskedIPAddress)), '') AS LocationID, CompanyBasicInformation.CompanyName,
CompanyVistedProfile.IPAddress, CompanyVistedProfile.CompanyId
FROM CompanyVistedProfile LEFT OUTER JOIN
CompanyBasicInformation ON CompanyVistedProfile.CompanyId = CompanyBasicInformation.ID
) AS tbl_Visitor ON L.locationID = tbl_Visitor.LocationID
Is the query fine?
I have crated index on IPLocationDB_ISP and IdxIPLocationDB_IP tables
-- Index on ISP
CREATE INDEX IXISP1 ON IPLocationDB_ISP(startIP) INCLUDE(endIP, isp)
-- Index on IPLocationDB_IP
Create Unique Index IdxIPLocationDB_IP on IPLocationDB_IP (StartIp) include (EndIP)
-- Script for Old view fron which i was fetching data
CREATE VIEW [dbo].[View_IspDetails]
AS
Select CompanyVistedProfile.*,
IPLocationDB_IP.StartIp
,IPLocationDB_IP.EndIp
,IPLocationDB_Location.LocationId
,IPLocationDB_Location.Country
,IPLocationDB_Location.Region
,IPLocationDB_Location.City
,IPLocationDB_Location.PostalCode
,IPLocationDB_Location.Latitude
,IPLocationDB_Location.Longitude
,IPLocationDB_Location.MetroCode
,IPLocationDB_Location.areacode
,IPLocationDB_Region.regionName
,isnull(dbo.IPLocationDB_Organization.Organization,'Not Recognised') as Organization --IPLocationDB_Organization.Organization
,IPLocationDB_ISP.ISP
,CompanyBasicInformation.CompanyName
from dbo.CompanyVistedProfile
left join CompanyBasicInformation on CompanyVistedProfile.companyId=CompanyBasicInformation.Id
left join dbo.IPLocationDB_IP On
dbo.CompanyVistedProfile.MaskedIpAddress>=dbo.IPLocationDB_IP.startIp
and dbo.CompanyVistedProfile.MaskedIpAddress <=dbo.IPLocationDB_IP.ENDIp
left join IPLocationDB_Location on IPLocationDB_IP.locationId = IPLocationDB_Location.LocationId
left join IPLocationDB_Region on IPLocationDB_Location.region =IPLocationDB_Region.regionCode and IPLocationDB_Location.country=IPLocationDB_Region.countryCode
left join dbo.IPLocationDB_Organization On
dbo.CompanyVistedProfile.MaskedIpAddress>=dbo.IPLocationDB_Organization.startIp
and dbo.CompanyVistedProfile.MaskedIpAddress <=dbo.IPLocationDB_Organization.ENDIp
left join dbo.IPLocationDB_ISP On
dbo.CompanyVistedProfile.MaskedIpAddress>=dbo.IPLocationDB_ISP.startIp
and dbo.CompanyVistedProfile.MaskedIpAddress <=dbo.IPLocationDB_ISP.ENDIp
I have attached some rows from IPLocationDB_ISP, IPLocationDB_IP, IPLocationDB_Location, & IPLocationDB_Region tables.
Please look into this..
June 23, 2010 at 1:44 am
Here I have attached actual execution plan
Thanks.
June 23, 2010 at 2:29 am
Your query looks ok to me. Is there still any problem ?
Of course you still need to test the code so the result is what you expect.
June 23, 2010 at 2:40 am
Thank u very much..
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply