7 Replies Latest reply on Mar 11, 2015 9:50 AM by gregor

    batch delete in sql commander

    RaymondChan

      hello

      is there any way to run several delete sql at the same time like:

       

      delete  from picomp2

      where picomp2.time >= '2013-01-01 00:00:00' and  picomp2.time < '2013-12-31 00:00:00'

      and picomp2.tag = 'a'

      delete  from picomp2

      where picomp2.time >= '2013-01-01 00:00:00' and  picomp2.time < '2013-12-31 00:00:00'

      and picomp2.tag = 'b'

      delete  from picomp2

      where picomp2.time >= '2013-01-01 00:00:00' and  picomp2.time < '2013-12-31 00:00:00'

      and picomp2.tag = 'c'

       

      Now it shows the error:

      [SQL Parser] [Line 4] Syntax error near 'delete'.

       

      Please advise.

      Thanks

       

       

        • Re: batch delete in sql commander
          Asle Frantzen

          Hi

           

          As long as your conditions are the same, why don't you try this:

           

          delete
          from picomp2 
          where picomp2.time >= '2013-01-01 00:00:00' and  picomp2.time < '2013-12-31 00:00:00'
          and picomp2.tag in ('a','b','c')
          
            • Re: batch delete in sql commander
              RaymondChan

              I got out of memory problem when tried to run them by 1 sql...

                • Re: batch delete in sql commander
                  dng

                  Hi Raymond,

                   

                  How many rows are returned when you run the query:

                  SELECT *
                  FROM [piarchive]..[picomp2]
                  WHERE tag in ('a','b','c')
                  AND time BETWEEN '2013-01-01 00:00:00' AND '2013-12-31 00:00:00'
                  
                  

                   

                  Is it possible to use a smaller time range? What is the end application where you are trying to do bulk delete of several tags for a year at a time? There might be other was to do this, depending on what you are trying to achieve.

                    • Re: batch delete in sql commander
                      RaymondChan

                      Theoretically, if combining 3 of the items, it will return around 1.x million rows.

                      But our server only have 4 GB ram and it already return out of memory error during execution.

                        • Re: batch delete in sql commander
                          Asle Frantzen

                          Try a "Select Count(*)" instead, to see the number of rows.

                           

                          The delete query shouldn't be returning any rows, as the delete query should be optimized to run in the PI server (i.e. not in the OLEDB client). When opening the connection to the PI server please try enabling logging to see what's going on in the background. In the "PI Server Login" dialog appearing when you double click your PI server in PI SQL Cmdr click "Options", set the log level to the highest number, and provide a full path to an empty .txt file where you want the logging to be written to. Then execute the query once, and review the log file content.

                          • Re: batch delete in sql commander
                            dng

                            Hi Raymond,

                             

                            What version of the PI server are you running? Is it a 32 or 64-bit PI server? Are you running SQL commander on the server, or on a client machine? In addition to looking at the SQL commander logs as Asle suggested, can you also check the PI server message logs for any error messages? You might be limited by PI server resources here. There are many safeguards on the PI server to limit expensive queries, but it seems that you are running out of system resources before hitting those limits. It is definitely worthwhile to look at where the bottleneck is.

                             

                            In addition, what is your query timeout setting on SQL commander (Tools > Options > Query > General)? How long after the query is initiated did you get the out of memory error?

                    • Re: batch delete in sql commander
                      gregor

                      Hello Raymond,

                       

                      Do you consider one of the provided answers / suggestions a sufficient solution for what you were looking for?

                      If so you can mark it as "Correct Answer"?

                      If none of the answers is sufficient to your question, please let us know and consider sharing additional insights.