Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Bloated tables

  1. #1
    Join Date
    Feb 2005
    Posts
    30

    Unanswered: Bloated tables

    I have a database which contains an audit table that fills up very quickly (approx. 4000 records are entered per day). Stored procedures are called via a web page which means that the response time is really slow (sometimes takes up to one minute for something to come back).
    I fixed this by changing the database so that the stored procedures are called by night (using a SQL job) and then entered into a temporary table which is then called instead of the stored procedure This way the data is already present in a table without the need to execute a select. The trade-off is that the data is only up-to-date from the night before (the previous way it was up-to-the minute) but this is not so important.
    However now this SLQ job is taking too long to run overnight (as the data is inreasing more each passing day) so I need a different way of doing it.
    I need to find a way of archiving the tables every three months (after this time the SQL job takes too long to run overnight) but I still need to select from the archived tables.
    Can anyone suggest a way of doing this?I could flatten the tables but then I can't perform any queries on them (I think).
    Thanks for any replies.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    from your description, it sounds like the queries are doing table scans (i.e. not optimized)

    have you declared any indexes on the tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    30
    Quote Originally Posted by r937 View Post
    from your description, it sounds like the queries are doing table scans (i.e. not optimized)

    have you declared any indexes on the tables?
    Yes, someone else designed the tables; there is a primary key called Audit_id and there are two indexes on it. One is nonclustered, unique and the other is clustered, unique.
    These are the only indexes that are on the table.
    I can't drop the table, change the design and insert all the data into it again as it is constantly being used, I am stuck with the table in it's present design.
    I have to resolve the problem by changing the procedures which access the table, I can't change the design.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Looks like you need to partition the table and put in some indexes based on your queries. What version of SQL Server are you on ?
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Feb 2005
    Posts
    30
    Quote Originally Posted by Enigma View Post
    Looks like you need to partition the table and put in some indexes based on your queries. What version of SQL Server are you on ?
    Version 10.0.1600

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Partitioning the data is one solution.
    Another might be an indexed view on the data, rather than a nightly batch process.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2005
    Posts
    30
    Having looked into partitioning the table, that seems unworkable because the table will have to be partitioned every time it gets too large (this could be every three months) and I won't be able to get at the table.
    This would mean new partitions being created continually and I would have to change all my procedures every time there is a new partition in order to select from each new partioned table.

  8. #8
    Join Date
    Feb 2005
    Posts
    30
    The procedures that are slowing down the access to the table are procedures that select from views but I have noticed there are no indexes on these views.
    The procedures select from the view, then enter what is returned into a temporary table, then does a select from what is in the temporary table.

    I think the views do not have indexes because they return a lot of columns. Also, there is no where clause.
    One of the views is below (this returns thirteen columns so even if I was to create an index on the view, which ones would I create it on?):
    Code:
    CREATE VIEW [dbo].[VIEW_KWH_REPORT_EWIT]
    AS
    SELECT       dbo.TBL_PC_AUDIT.AUDIT_ID,dbo.TBL_PC_AUDIT.PC_PROFILE_ID, dbo.TBL_PC_AUDIT.EWIT_SHUTDOWN_TIME, dbo.TBL_PC_AUDIT.EWIT_HIBERNATE_TIME, 
                          dbo.TBL_PC_AUDIT.EWIT_SLEEP_TIME,  dbo.TBL_PC_AUDIT.EWIT_F12_TIME, dbo.TBL_PC_AUDIT.STARTUP_TIME, dbo.TBL_PC_AUDIT.STATUS, 
                          dbo.TBL_PC_DESCRIPTION.PC_POWER_RATING, dbo.TBL_SUB_POLICY.SUB_POLICY_NAME, dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION, 
                          dbo.TBL_WORKSTATION.WORKSTATION_OWNER, TBL_PC_PROFILE_1.PC_NAME
    FROM         dbo.TBL_PC_PROFILE AS TBL_PC_PROFILE_1 INNER JOIN
                          dbo.TBL_PC_AUDIT INNER JOIN
                          dbo.TBL_ELECTRICITY_RATES ON dbo.TBL_PC_AUDIT.RATE_ID = dbo.TBL_ELECTRICITY_RATES.RATE_ID INNER JOIN
                          dbo.TBL_PC_PROFILE ON dbo.TBL_PC_AUDIT.PC_PROFILE_ID = dbo.TBL_PC_PROFILE.PC_PROFILE_ID INNER JOIN
                          dbo.TBL_PC_DESCRIPTION ON dbo.TBL_PC_PROFILE.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID INNER JOIN
                          dbo.TBL_WORKSTATION ON dbo.TBL_PC_PROFILE.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID ON 
                          TBL_PC_PROFILE_1.PC_PROFILE_ID = dbo.TBL_PC_AUDIT.PC_PROFILE_ID AND 
                          TBL_PC_PROFILE_1.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID AND 
                          TBL_PC_PROFILE_1.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID INNER JOIN
                          dbo.TBL_SUB_POLICY ON dbo.TBL_PC_AUDIT.SUB_POLICY_ID = dbo.TBL_SUB_POLICY.SUB_POLICY_ID
    
    
    GO

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alonzo View Post
    One of the views is below (this returns thirteen columns so even if I was to create an index on the view, which ones would I create it on?):
    any and/or all of the columns mentioned in the ON clauses

    by the way, one reason for the query performing poorly might be because the joins appear to be malformed

    here i've reformatted your query to make it easier to read

    see anything unusual?
    Code:
    CREATE VIEW [dbo].[VIEW_KWH_REPORT_EWIT]
    AS
    SELECT dbo.TBL_PC_AUDIT.AUDIT_ID
         , dbo.TBL_PC_AUDIT.PC_PROFILE_ID
         , dbo.TBL_PC_AUDIT.EWIT_SHUTDOWN_TIME
         , dbo.TBL_PC_AUDIT.EWIT_HIBERNATE_TIME
         , dbo.TBL_PC_AUDIT.EWIT_SLEEP_TIME
         , dbo.TBL_PC_AUDIT.EWIT_F12_TIME
         , dbo.TBL_PC_AUDIT.STARTUP_TIME
         , dbo.TBL_PC_AUDIT.STATUS
         , dbo.TBL_PC_DESCRIPTION.PC_POWER_RATING
         , dbo.TBL_SUB_POLICY.SUB_POLICY_NAME
         , dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION
         , dbo.TBL_WORKSTATION.WORKSTATION_OWNER
         , TBL_PC_PROFILE_1.PC_NAME
      FROM dbo.TBL_PC_PROFILE AS TBL_PC_PROFILE_1 
    INNER
      JOIN dbo.TBL_PC_AUDIT 
        /* missing ON clause */
    INNER
      JOIN dbo.TBL_ELECTRICITY_RATES 
        ON dbo.TBL_PC_AUDIT.RATE_ID = dbo.TBL_ELECTRICITY_RATES.RATE_ID 
    INNER
      JOIN dbo.TBL_PC_PROFILE 
        ON dbo.TBL_PC_AUDIT.PC_PROFILE_ID = dbo.TBL_PC_PROFILE.PC_PROFILE_ID 
    INNER
      JOIN dbo.TBL_PC_DESCRIPTION 
        ON dbo.TBL_PC_PROFILE.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID 
    INNER
      JOIN dbo.TBL_WORKSTATION 
        ON dbo.TBL_PC_PROFILE.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID 
        ON TBL_PC_PROFILE_1.PC_PROFILE_ID = dbo.TBL_PC_AUDIT.PC_PROFILE_ID 
       AND TBL_PC_PROFILE_1.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID 
       AND TBL_PC_PROFILE_1.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID 
    INNER
      JOIN dbo.TBL_SUB_POLICY 
        ON dbo.TBL_PC_AUDIT.SUB_POLICY_ID = dbo.TBL_SUB_POLICY.SUB_POLICY_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Rudy,

    I did the exact same thing as you . . .

    . . . it is amazing what you can't see when your queries are poorly formatted !!!

    My formatting is similar to your's . . .

    Code:
    SELECT	dbo.TBL_PC_AUDIT.AUDIT_ID
    	,dbo.TBL_PC_AUDIT.PC_PROFILE_ID
    	,dbo.TBL_PC_AUDIT.EWIT_SHUTDOWN_TIME
    	,dbo.TBL_PC_AUDIT.EWIT_HIBERNATE_TIME
    	,dbo.TBL_PC_AUDIT.EWIT_SLEEP_TIME
    	,dbo.TBL_PC_AUDIT.EWIT_F12_TIME
    	,dbo.TBL_PC_AUDIT.STARTUP_TIME
    	,dbo.TBL_PC_AUDIT.STATUS
    	,dbo.TBL_PC_DESCRIPTION.PC_POWER_RATING
    	,dbo.TBL_SUB_POLICY.SUB_POLICY_NAME
    	,dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION
    	,dbo.TBL_WORKSTATION.WORKSTATION_OWNER
    	,TBL_PC_PROFILE_1.PC_NAME
    FROM	dbo.TBL_PC_PROFILE AS TBL_PC_PROFILE_1
    INNER
    JOIN	dbo.TBL_PC_AUDIT
    INNER
    JOIN	dbo.TBL_ELECTRICITY_RATES ON
    		dbo.TBL_PC_AUDIT.RATE_ID = dbo.TBL_ELECTRICITY_RATES.RATE_ID
    INNER
    JOIN	dbo.TBL_PC_PROFILE ON
    		dbo.TBL_PC_AUDIT.PC_PROFILE_ID = dbo.TBL_PC_PROFILE.PC_PROFILE_ID
    INNER
    JOIN	dbo.TBL_PC_DESCRIPTION ON
    		dbo.TBL_PC_PROFILE.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID
    INNER
    JOIN	dbo.TBL_WORKSTATION ON
    		dbo.TBL_PC_PROFILE.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID
    	ON 
    		TBL_PC_PROFILE_1.PC_PROFILE_ID = dbo.TBL_PC_AUDIT.PC_PROFILE_ID
    		AND TBL_PC_PROFILE_1.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID
    		AND TBL_PC_PROFILE_1.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID
    INNER
    JOIN	dbo.TBL_SUB_POLICY ON
    		dbo.TBL_PC_AUDIT.SUB_POLICY_ID = dbo.TBL_SUB_POLICY.SUB_POLICY_ID
    Alonzo . . .

    What is happening there with the multiple ON clauses for the TBL_WORKSTATION table? Did your copy/paste drop something?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Feb 2005
    Posts
    30
    There are mistakes in the SQL as they were written by someone else.
    I tried to create an index on the view but I couldn't (cannot create index on view because the view is not schema bound)

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    BESIDES All of this poorly designed architecture

    No one has mentioned how many rows are in these tables

    I'm guessing it's not gonna be a lot

    The View doesn't need a where clause

    You usually supply on when you select from it

    More to the point

    What operationally do you need these things to do?

    What's the Business Requirements?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alonzo View Post
    There are mistakes in the SQL as they were written by someone else.
    and you cannot fix them why?

    Quote Originally Posted by alonzo View Post
    I tried to create an index on the view but I couldn't (cannot create index on view because the view is not schema bound)
    i would index the underlying tables instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2005
    Posts
    30
    There are too many mistakes to correct in the views, procedures. I know that is what needs to be done but I will not be given the time as the database is filling up and if I get bogged down with changing queries, views etc. the table will soon become totally frozen.
    Also,it would require changes to the design of the underlying table(s) and I cannot do this as they are being accessed 24/7 and cannot be taken offline
    The main cause of the slowness is in the stored procedure that calls the view I posted.
    It does the following:
    1. Creates a temporary table
    2. Inserts data into the newly created table
    3. Updates it
    4. Selects data from the temporary table

    Obviously this takes up a lot of time.
    I have posted it below.
    A lot of the other stored procedures do the same. They worked OK when there wa not a lot of data in the table but now that it is filling up it is a problem.
    If I could change the steps above to something quicker to execute that would buy me some time.
    I am thinking using derived tables may be a suitable/quicker alternative?
    Code:
    GO
    /****** Object:  StoredProcedure [dbo].[SP_ACCUMLATIVE_MONTH_KWH_EWIT]    Script Date: 05/21/2012 16:41:28 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    
    /*
    Name: SP_SAVINGS_REPORT
    Description:Accumlative KWH Savings Report per Month
    */
    
    create PROCEDURE [dbo].[SP_ACCUMLATIVE_MONTH_KWH_EWIT]	
    	
    
    AS	
    
    
    	/* Create temp table */
    	CREATE TABLE #KWHtemp
    	(
    	pc_profile_id int,
    	ewit_shutdown_Time datetime NULL,
    	ewit_hibernate_Time datetime NULL, 
    	ewit_sleep_Time datetime NULL,
    	ewit_f12_Time datetime NULL,  
    	startup_Time datetime NULL,
    	status varchar(50),
    	subpolicy_name varchar(50),
    	total_hrs_off float, 
    	pc_kwh_rate float,
    	KWH_savings float
    	)
    	
    	
    	/** Insert Values into Temp Table from View_Savings_Report*/
    	
    	insert into #KWHtemp (pc_profile_id, ewit_shutdown_Time, ewit_hibernate_Time, ewit_sleep_Time, ewit_f12_Time, startup_Time, status, pc_kwh_rate, subpolicy_name)
    	SELECT PC_PROFILE_ID, EWIT_SHUTDOWN_TIME, EWIT_HIBERNATE_TIME, EWIT_SLEEP_TIME,EWIT_F12_TIME, STARTUP_TIME, STATUS, PC_POWER_RATING, SUB_POLICY_NAME
    	FROM VIEW_KWH_REPORT_EWIT
    	
    
    	/** Get total hours computer is off*/
    	UPDATE #KWHtemp
    	SET total_hrs_off = DATEDIFF(MINUTE, 
    	COALESCE(ewit_shutdown_Time, ewit_hibernate_Time, ewit_sleep_Time, ewit_f12_Time,startup_Time),
    	COALESCE(startup_Time, ewit_f12_Time,ewit_sleep_Time,ewit_hibernate_Time,ewit_shutdown_Time))/ 60.0
    	
    
    	
    	/** Calculate Total KWH Savings*/ 
    	UPDATE #KWHtemp
    	SET KWH_savings = (isnull(total_hrs_off, 0) * pc_kwh_rate)
    
    	
    	/**Calculate Accumlative Savings per Year/Month*/       
       ;with cte as
    (
            SELECT 
             datepart(yyyy,st.startup_Time) as 'Year', 
             datepart(month,st.startup_Time) as 'Month_Num', 
            datename(m,st.startup_Time) as 'Month',
                    (     
                            SELECT SUM(st2.KWH_savings)            
                            FROM #KWHtemp st2           
                            WHERE
                            st2.startup_Time     <= st.startup_Time
                    ) AS AccountRunningTotal   
            FROM #KWHtemp st  
    )
    SELECT [Year], [Month],max(AccountRunningTotal) AS KWH_savings
    FROM cte
    GROUP BY [Year], [Month], [Month_Num]
    ORDER BY [Year], [Month_Num] 
    
    
    	
    	RETURN

  15. #15
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by r937 View Post
    any and/or all of the columns mentioned in the ON clauses

    by the way, one reason for the query performing poorly might be because the joins appear to be malformed

    here i've reformatted your query to make it easier to read

    see anything unusual?
    Code:
    CREATE VIEW [dbo].[VIEW_KWH_REPORT_EWIT]
    AS
    SELECT dbo.TBL_PC_AUDIT.AUDIT_ID
         , dbo.TBL_PC_AUDIT.PC_PROFILE_ID
         , dbo.TBL_PC_AUDIT.EWIT_SHUTDOWN_TIME
         , dbo.TBL_PC_AUDIT.EWIT_HIBERNATE_TIME
         , dbo.TBL_PC_AUDIT.EWIT_SLEEP_TIME
         , dbo.TBL_PC_AUDIT.EWIT_F12_TIME
         , dbo.TBL_PC_AUDIT.STARTUP_TIME
         , dbo.TBL_PC_AUDIT.STATUS
         , dbo.TBL_PC_DESCRIPTION.PC_POWER_RATING
         , dbo.TBL_SUB_POLICY.SUB_POLICY_NAME
         , dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION
         , dbo.TBL_WORKSTATION.WORKSTATION_OWNER
         , TBL_PC_PROFILE_1.PC_NAME
      FROM dbo.TBL_PC_PROFILE AS TBL_PC_PROFILE_1 
    INNER
      JOIN dbo.TBL_PC_AUDIT 
        /* missing ON clause */
    INNER
      JOIN dbo.TBL_ELECTRICITY_RATES 
        ON dbo.TBL_PC_AUDIT.RATE_ID = dbo.TBL_ELECTRICITY_RATES.RATE_ID 
    INNER
      JOIN dbo.TBL_PC_PROFILE 
        ON dbo.TBL_PC_AUDIT.PC_PROFILE_ID = dbo.TBL_PC_PROFILE.PC_PROFILE_ID 
    INNER
      JOIN dbo.TBL_PC_DESCRIPTION 
        ON dbo.TBL_PC_PROFILE.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID 
    INNER
      JOIN dbo.TBL_WORKSTATION 
        ON dbo.TBL_PC_PROFILE.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID 
        ON TBL_PC_PROFILE_1.PC_PROFILE_ID = dbo.TBL_PC_AUDIT.PC_PROFILE_ID 
       AND TBL_PC_PROFILE_1.PC_DESCRIPTION_ID = dbo.TBL_PC_DESCRIPTION.PC_DESCRIPTION_ID 
       AND TBL_PC_PROFILE_1.WORKSTATION_ID = dbo.TBL_WORKSTATION.WORKSTATION_ID 
    INNER
      JOIN dbo.TBL_SUB_POLICY 
        ON dbo.TBL_PC_AUDIT.SUB_POLICY_ID = dbo.TBL_SUB_POLICY.SUB_POLICY_ID
    Having seen a query similar to this where all the "ON" clauses were bumped together, which was a nightmare to read but still worked. So, I would say no issues other than badly formatted sql ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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