Slow Query Performance (View)

Question

Post reply

Slow Query Performance (View)

niteen2009

SSC Veteran

Points: 299
More actions
June 22, 2010 at 2:34 am

#225885

Hi everyone
Can anyone optimise this query?
SELECT CompanyVistedProfile.VisitedDate, ISNULL(IPLocationDB_Organization.organization, 'Not Recognised') AS Organization,
CompanyBasicInformation.CompanyName, CompanyVistedProfile.IPAddress
FROM CompanyVistedProfile LEFT OUTER JOIN
CompanyBasicInformation ON CompanyVistedProfile.CompanyId = CompanyBasicInformation.ID LEFT OUTER JOIN
IPLocationDB_Organization ON CompanyVistedProfile.MaskedIPAddress >= IPLocationDB_Organization.startIP AND
CompanyVistedProfile.MaskedIPAddress <= IPLocationDB_Organization.endIP
Thanks.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Please post DDL Etc as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Clear Sky SQL
My Blog[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Not enough information here.

Try taking a look at this article and post all the information required to start tuning this query:

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That triangular join you have there (join on >=) is not likely to perform well on large rowcounts. What is it that you're trying to do here?

How many rows are there in the tables that the view references?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

niteen2009 SSC Veteran Points: 299 More actions · Answer 4

I have attached Query execution plan and some rows of tables..

Thanks

niteen2009 SSC Veteran Points: 299 More actions · Answer 5

HI,

I have to track the visitor details who visited to companies in my database. I am tracking their datails using there Ip address. I converts that IP into masked IP and save it into my database. I have table of masked IP address. Using the aboove query a get the organization name of visited person.

The masked IP address table have near about 17 Lakhs row of containing ID, StartIP, EndIP, Orgnization Name.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Table definitions and index definitions as well please.

How many rows (approx) in the tables?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

Indexes and DDL as well please

Clear Sky SQL
My Blog[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Looking at the exec plan there's an implicit conversion that sure doesn't help.

You could have posted DDL scripts (tables and indexes) and scripted out data as INSERT statements.

I don't have time to do this for you: will you help us help you?

-- Gianluca Sartori

niteen2009 SSC Veteran Points: 299 More actions · Answer 9

CREATE TABLE [dbo].[IPLocationDB_Organization](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[startIP] [nvarchar](100) NULL,

[endIP] [nvarchar](100) NULL,

[organization] [nvarchar](4000) NULL,

CONSTRAINT [PK_IPLocationDB_Organization] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[CompanyVistedProfile](

[Id] [bigint] IDENTITY(1,1) NOT NULL,

[VisitedDate] [datetime] NOT NULL CONSTRAINT [DF_CompanyVistedProfile_VisitedDate] DEFAULT (getdate()),

[IPAddress] [varchar](20) NULL,

[CompanyId] [int] NOT NULL,

[RefferarUri] [varchar](350) NULL,

[MaskedIPAddress] [bigint] NULL,

CONSTRAINT [PK_CompanyVistedProfile] PRIMARY KEY CLUSTERED

(

[Id] 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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

Dave Ballantyne

SSC-Dedicated

Points: 33667

June 22, 2010 at 3:00 am

#1183158

No Indexes ?

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Are you always querying the entire table? No limitation on portions of the visitors? No where clause?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

niteen2009 SSC Veteran Points: 299 More actions · Answer 12

Only Primary Key. No Indexes.

I am fetching all the rows using that above query.

IS any other wat to achieve my target the please tell me.

Thanks

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

Ideally you should modify your tables so that and the IPAddress type columns are of the same type, bigint would be best IMO.

You need indexing though. try

Create Unique Index IdxIPLocationDB_OrganizationStartEndIp on IPLocationDB_Organization (StartIp) include (EndId)

For starters

Clear Sky SQL
My Blog[/url]

niteen2009 SSC Veteran Points: 299 More actions · Answer 14

Thanks for reply.

And what about fetching records (Select Query) as I want the organization Name.

Is there any other way with that select query to get records in faster way (as there are too much records in organization table)?

And one important note is that the data in Organization table updates on regular basis depends on the IP structure.