Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: SQL Server Trigger timing out Access Append Query

    I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.

    The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.
    I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.

    I have tested the trigger to do a simple task, and this works.
    I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables

    When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.

    I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!

    Does this sound right? Did I guess the issue correctly, and is there a workaround?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's kind of numbers make up "huge tables"?
    Can you post your trigger code?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2013
    Posts
    4
    Quote Originally Posted by gvee View Post
    What's kind of numbers make up "huge tables"?
    Can you post your trigger code?
    Thanks for replying

    Each table holds around 7million rows, but this will grow by about 5 times when it's complete. On the other hand it will be running on a much quicker server then.


    Here is the trigger, though I have only left one example of the tables being created. the rest are very similar.

    Code:
    USE [AVLDB1]
    GO
    /****** Object:  Trigger [dbo].[aggregation]    Script Date: 10/17/2013 11:09:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[aggregation] 
       ON  [dbo].[TriggerTable] 
       AFTER UPDATE
    AS 
    IF EXISTS (SELECT * FROM inserted WHERE Date = CONVERT(date, getdate()) and TriggerCount = 2)
      
    BEGIN
    	
    	
    DROP TABLE [ATM History Monthly] ;
    SELECT Dates.YearMonth AS Month
      ,Brand
      ,[ATM HISTORY].Channel
      ,Project
      ,"ATM ID"
      ,fault_id
      ,FAULT
      ,CATEGORY
      ,component_type
      ,sum("DTIME concurrent") AS "DTIME concurrent"
      ,sum("CR Count") AS "CR Count"
      ,sum("CR Count 1 Min") AS "CR Count 1 Min"
      ,"Service Level" AS "Service Level"
      ,LocationR
      ,sum("new fault count") AS "new fault count"
    INTO [ATM History Monthly] 
    from AVLDB1.dbo.[ATM HISTORY]
    inner join dbo.Dates on [ATM HISTORY].Date = Dates.Report_Dates
    inner join dbo.eiManager_Static_Data on [ATM HISTORY]."ATM ID" = [eiManager_Static_Data].Device_Id
    group by Dates.YearMonth,Brand,[ATM HISTORY].Channel,Project,"ATM ID",fault_id,FAULT,CATEGORY,component_type  ,"Service Level",LocationR;
    	
    END
    should Access have to wait until the trigger is complete?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    So every time your table is updated and those conditions are met, you want to drop and completely rebuild an entire 7 million row table? Yeah I can see where that might time time out in most environments. I think you need to rethink your solution and your design because this thing is not built for speed.

    I am hesitant to give you this particular solution, because I really do not think you should be doing this kind of thing in a trigger. The Access front end times out because the trigger is inside the scope of the update transaction. What you could do is have the trigger kick off a SQL Agent job as I recall, which would not be in the scope of the transaction as I recall, but this is really a shaky kludge to support a questionable solution design. If you do go down this path, I would include a query that checks the system tables or views to see if the job is already running.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Oct 2013
    Posts
    4
    Quote Originally Posted by Thrasymachus View Post
    So every time your table is updated and those conditions are met, you want to drop and completely rebuild an entire 7 million row table? Yeah I can see where that might time time out in most environments. I think you need to rethink your solution and your design because this thing is not built for speed.

    I am hesitant to give you this particular solution, because I really do not think you should be doing this kind of thing in a trigger. The Access front end times out because the trigger is inside the scope of the update transaction. What you could do is have the trigger kick off a SQL Agent job as I recall, which would not be in the scope of the transaction as I recall, but this is really a shaky kludge to support a questionable solution design. If you do go down this path, I would include a query that checks the system tables or views to see if the job is already running.
    Thanks Thrasymachus

    The job will only run once per day at around 1 in the morning. There are actually 18 of these tables so far unless we decide to aggregate more!

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Then it sounds like it should not be a trigger execution at all. Why not just write the whole process in stored procedures and make them individual steps in a SQL Agent job and schedule it to run? I would ditch the trigger and the Access stuff and do that. Then you do not have to worry about Access time outs at all.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    +1.
    Thrasy is spot on here.
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, you should rethink rebuilding these aggregation tables each time you insert a new record. Why wouldn't you just update the existing aggregation or if no previous record insert new to your aggregate tables? Much better solution than rebuilding your wheel each time you put another brick on the wagon.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I second that. Incremental changes instead of complete rebuilds are definitely more scalable in the long run. It almost sounds like a case for feeding some change data capture (CDC) tables and then having a SQL Agent job to update your aggregate tables.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Heh dav1mo,

    Richmond? Looking for a job? I need a couple of cracker jack sql server ssis people in Fairfax county? I know that is a hike.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Oct 2013
    Posts
    4
    Thanks all.

    The SQL Server Agenmt route sounds like the best one, though it is not something I have dealt with before. I'lls ee if I can get it working.

    Unfortunately data will change historically- and it could be any bit, so it does all need rebuilding each night (there are probably ways to avoid this, but technically i'm not so hot with SQL Server!), also if you look at the trigger I have used, there is a specific trigger table which is appended to after all of the access append is completed. It's actually quite a lot more complicated on the Access side, involving multiple Access databases, something we will be looking to put into SQL Server in the end, however just don't have the time to do now.

Tags for this Thread

Posting Permissions

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