Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    9

    Unanswered: Clustered and Non-clustered Index

    Hi,

    I have one table having more than 3 millions rows with following columns:

    [SITE_ID]

    [USER_NAME]

    [ACTIVITY_NAME]

    [PAGE_TYPE]

    [ACTION]

    [DOC_ID]

    [FROM_PAGE]

    [TO_PAGE]

    [ORG_NAME]

    [LOG_DATE_TIME]

    [SESSION_ID]

    [IP]

    [MACHINE_ID]



    I am using 8 different queries, all thru stored procedure, to fetch data.

    SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.

    I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.

    Problem is that data fetching without indexs is faster than when creating idex.

    Is there any problem in index columns.

    Can you please suggest me a better index plan.

    I have attached the queries / column table image file.

    Images file described columns called details
    <br /><img src='http://xs434.xs.to/xs434/08513/queries-columns997.jpg'><br>
    (http://xs434.xs.to/xs434/08513/queries-columns997.jpg or
    http://xs.to/xs.php?h=xs434&d=08513&...columns997.jpg)


    Please help..


    Thanks

    Sharma
    Attached Thumbnails Attached Thumbnails Queries-Columns.jpg  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the code for the worst behaving stored procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2008
    Posts
    9
    ALTER PROCEDURE [dbo].[SP_ReportAppST]
    -- Add the parameters for the stored procedure here
    @task varchar(20) = null,
    @ReportType varchar(500) = null,
    @SiteId varchar(25) = null,
    @Params varchar(8000) = null,
    @RetVal varchar(8000) = null,
    @DbName varchar(50) = null
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SET @DbName = 'DBbackup.dbo.SiteLog'

    if @task='Report'
    begin

    if @ReportType='LogByDateDetail'
    begin
    select @RetVal= 'SELECT dbo_LOG.LOG_DATE_TIME, dbo_LOG.USER_NAME, dbo_LOG.ACTION, dbo_LOG.FROM_PAGE,
    dbo_LOG.ORG_NAME, dbo_LOG.SESSION_ID, dbo_LOG.ACTIVITY_NAME FROM '+@DbName+' dbo_LOG '+@Params
    end

    if @ReportType='LogByUserDetail'
    begin
    select @RetVal= 'SELECT dbo_LOG.LOG_DATE_TIME, dbo_LOG.USER_NAME, dbo_LOG.ACTION, dbo_LOG.FROM_PAGE, dbo_LOG.ORG_NAME, dbo_LOG.SESSION_ID, dbo_LOG.ACTIVITY_NAME
    FROM '+@DbName+' dbo_LOG '+@Params+' ORDER BY dbo_LOG.USER_NAME'
    end

    if @ReportType='LogByCompanyDetail'
    begin
    select @RetVal= 'SELECT dbo_LOG.LOG_DATE_TIME, dbo_LOG.ACTION, dbo_LOG.ORG_NAME, dbo_LOG.SESSION_ID,
    dbo_LOG.ACTIVITY_NAME FROM '+@DbName+' dbo_LOG '+@Params+'
    ORDER BY dbo_LOG.ORG_NAME, dbo_LOG.ACTIVITY_NAME, dbo_LOG.SESSION_ID, dbo_LOG.LOG_DATE_TIME '
    end

    if @ReportType='LogByDocumentDetail'
    begin
    select @RetVal= 'SELECT dbo_LOG.LOG_DATE_TIME, dbo_LOG.USER_NAME, dbo_LOG.ACTION, dbo_LOG.FROM_PAGE, dbo_LOG.ORG_NAME, dbo_LOG.SESSION_ID, dbo_LOG.ACTIVITY_NAME
    FROM '+@DbName+' dbo_LOG '+@Params
    end

    if @ReportType='GraphByDocumentTop10'
    begin
    select @RetVal= 'SELECT LOG.ORG_NAME, LOG.SITE_ID, LOG.ACTION, LOG.ACTIVITY_NAME
    FROM '+@DbName+' LOG '+@Params+' AND LOG.ACTION IN (''Open'',''Read'')'
    end

    if @ReportType='GraphByCompanyTop5'
    begin
    select @RetVal= 'SELECT LOG.ORG_NAME, LOG.SITE_ID, LOG.ACTION
    FROM '+@DbName+' LOG '+@Params+' AND LOG.ACTION IN (''Open'',''Read'') ORDER BY LOG.ORG_NAME'
    end

    if @ReportType='GraphByCompany'
    begin
    select @RetVal= 'SELECT LOG.ORG_NAME, LOG.SITE_ID, LOG.ACTION, LOG.LOG_DATE_TIME
    FROM '+@DbName+' LOG '+@Params+' AND LOG.ACTION IN (''Open'',''Read'')'
    end

    if @ReportType='AllActivityReport'
    begin
    select @RetVal= 'SELECT LOG.USER_NAME, LOG.ACTIVITY_NAME, LOG.PAGE_TYPE, LOG.ACTION, LOG.LOG_DATE_TIME, LOG.ORG_NAME
    FROM '+@DbName+' LOG '+@Params+' ORDER BY LOG.LOG_DATE_TIME'
    end
    end

    if @task='CheckData'
    begin
    select @RetVal= 'SELECT COUNT(*) FROM '+@DbName+' WHERE SITE_ID= '+@SiteId
    end

    if @task='ORG_NAME'
    begin
    select @RetVal= 'SELECT DISTINCT ORG_NAME FROM '+@DbName+'
    WHERE SITE_ID= '+@SiteId+' AND ACTION IN ('+@ReportType+') AND ORG_NAME <> ''''
    AND ORG_NAME NOT IN ('+@Params+') ORDER BY ORG_NAME'
    end

    if @task='USER_NAME'
    begin
    select @RetVal= 'SELECT DISTINCT USER_NAME FROM '+@DbName+'
    WHERE SITE_ID= '+@SiteId+' AND ORG_NAME <> '''' AND ORG_NAME IN ('+@ReportType+')
    AND ORG_NAME NOT IN ('+@Params+')
    AND USER_NAME <> '''' ORDER BY USER_NAME'
    end

    execute (@RetVal)

    END

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WTF???

    Create a separate procedure for each "task"

    What kind of string are you passing in the @Params parameter? Please tell me it is not a WHERE clause.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Also, I assume this is returning metadata about various databases?

    Abandon the idea of a separate table each database. Combine the data into a single table with a column designating the database. Then dump the dynamic SQL approach.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •