Post reply

Slow Query Performance (View)

  • 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.

  • June 22, 2010 at 2:36 am

    #1183141

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


    Clear Sky SQL
    My Blog[/url]

  • June 22, 2010 at 2:37 am

    #1183142

    Not enough information here.

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

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

    -- Gianluca Sartori

  • June 22, 2010 at 2:38 am

    #1183143

    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

  • June 22, 2010 at 2:48 am

    #1183145

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

    Thanks

  • June 22, 2010 at 2:52 am

    #1183148

    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.

  • June 22, 2010 at 2:53 am

    #1183150

    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

  • June 22, 2010 at 2:55 am

    #1183153

    Indexes and DDL as well please


    Clear Sky SQL
    My Blog[/url]

  • June 22, 2010 at 2:55 am

    #1183154

    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

  • June 22, 2010 at 2:56 am

    #1183156

    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

  • June 22, 2010 at 3:00 am

    #1183158

    No Indexes ?


    Clear Sky SQL
    My Blog[/url]

  • June 22, 2010 at 3:06 am

    #1183164

    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

  • June 22, 2010 at 3:20 am

    #1183172

    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

  • June 22, 2010 at 3:32 am

    #1183177

    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]

  • June 22, 2010 at 3:37 am

    #1183179

    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.

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

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