Query taking longer than expected odbc sage

Question

I am doing a simple select with a date filter on it with a months range where only 32 records are present however its taking 15 seconds to query and return the data I am using sage 50 as you can probally tell and c#. I am using odbc to create the query the seem speeds can be found if i use the odbc query tool.

This is for a stright forward select and it should not be taking that long to return the data through odbc.

String SQL = string.Format("SELECT 'ORDER_NUMBER', 'ORDER_OR_QUOTE', 'ANALYSIS_1','ACCOUNT_REF','ORDER_DATE','NAME', 'COURIER_NUMBER','COURIER_NAME','CUST_TEL_NUMBER' ,'DESPATCH_DATE','ACCOUNT_REF', 'DEL_NAME', 'DEL_ADDRESS_1', 'DEL_ADDRESS_2', 'DEL_ADDRESS_3', 'DEL_ADDRESS_4', 'DEL_ADDRESS_5', 'INVOICE_NUMBER','ORDER_DATE','INVOICE_NUMBER_NUMERIC', 'CONTACT_NAME','CONSIGNMENT', 'NOTES_1', 'ITEMS_NET' ,'ITEMS_GROSS','QUOTE_STATUS' FROM SALES_ORDER WHERE ORDER_DATE

='{0}' and ORDER_DATE <='{1}'", fromD, toD);

public List<SalesOrders> GetSalesOrders()
{
        List<SalesOrders> _salesOrdersList = new List<SalesOrders>();

        try
        {


            string sageDsn = ConfigurationManager.AppSettings["SageDSN"];
            string sageUsername = ConfigurationManager.AppSettings["SageUsername"];
            string sagePassword = ConfigurationManager.AppSettings["SagePassword"];
            //int totalRecords = GetSalesOrdersount();
            int counter = 0;
             //using (var connection = new OdbcConnection("DSN=SageLine50v24;Uid=Manager;Pwd=;"))
            using (var connection = new OdbcConnection(String.Format("DSN={0};Uid={1};Pwd={2};", sageDsn, sageUsername, sagePassword)))
            {

                connection.Open();

                //string sql = string.Format(getInvoiceSql, customerCode, DateTime.Today.AddMonths(-1).ToString("yyyy-MM-dd"));
                string fromD = dtpFrom.Value.ToString("yyyy-MM-dd");
                string toD = dtpTo.Value.ToString("yyyy-MM-dd");

                String SQL = string.Format("SELECT 'ORDER_NUMBER', 'ORDER_OR_QUOTE', 'ANALYSIS_1','ACCOUNT_REF','ORDER_DATE','NAME', 'COURIER_NUMBER','COURIER_NAME','CUST_TEL_NUMBER' ,'DESPATCH_DATE','ACCOUNT_REF',  'DEL_NAME', 'DEL_ADDRESS_1', 'DEL_ADDRESS_2', 'DEL_ADDRESS_3', 'DEL_ADDRESS_4', 'DEL_ADDRESS_5',  'INVOICE_NUMBER','ORDER_DATE','INVOICE_NUMBER_NUMERIC', 'CONTACT_NAME','CONSIGNMENT', 'NOTES_1', 'ITEMS_NET' ,'ITEMS_GROSS','QUOTE_STATUS' FROM SALES_ORDER WHERE ORDER_DATE >='{0}' and ORDER_DATE <='{1}'", fromD, toD);
                using (var command = new OdbcCommand(SQL, connection))
                {


                    using (var reader = command.ExecuteReader())

                    {

                        while (reader.Read())
                        {
                            counter++;

                            backgroundWorker1.ReportProgress(counter);

                            var salesOrders = new SalesOrders();
                            salesOrders.ACCOUNT_REF = Convert.ToString(reader["ACCOUNT_REF"]);
                            salesOrders.RecordIdentifier = "";
                            salesOrders.ShipmmentId = Convert.ToString(reader["ORDER_NUMBER"]);
                            salesOrders.OrderDate = Convert.ToDateTime(reader["ORDER_DATE"]);
                            salesOrders.OrderNumber = Convert.ToString(reader["ORDER_NUMBER"]);

                            salesOrders.Company = "";
                            salesOrders.Carrier = Convert.ToString(reader["COURIER_NUMBER"]);
                            salesOrders.CarrierService = Convert.ToString(reader["COURIER_NAME"]);
                            salesOrders.CustomerName = Convert.ToString(reader["NAME"]);
                            salesOrders.ShipToAddress1 = Convert.ToString(reader["DEL_ADDRESS_1"]);
                            salesOrders.ShipToAddress2 = Convert.ToString(reader["DEL_ADDRESS_2"]);
                            salesOrders.ShipToAddress3 = Convert.ToString(reader["DEL_ADDRESS_3"]);
                            salesOrders.ShipToAddress4 = Convert.ToString(reader["DEL_ADDRESS_4"]);
                            salesOrders.ShipToAddress5 = Convert.ToString(reader["DEL_ADDRESS_5"]);
                            salesOrders.ShiptoAttention = Convert.ToString(reader["DEL_NAME"]);
                            salesOrders.ShiptoPhoneNo = Convert.ToString(reader["CUST_TEL_NUMBER"]);
                            salesOrders.Country = Convert.ToString(reader["ANALYSIS_1"]);
                            salesOrders.ShiptoEmail = "";
                            salesOrders.MakeAddressDefault = "Y";
                            bool isProcessed = _sqlManager.hasbeenProcessed(salesOrders.OrderNumber);
                            if (isProcessed == true)
                                salesOrders.Exported = true;
                            _salesOrdersList.Add(salesOrders);


                        }
                    }
                }

            }

        return _salesOrdersList.OrderByDescending(o => o.OrderDate).ToList();
    }

@PanagiotisKanavos yes in the sage database it is indexed thanks for asking this is a third party product I am intergrating into — david, Sep 11 '18 at 15:34
Is there any way of reporting back the time of the executate reader to the end user at least ? — david, Sep 11 '18 at 15:35
Nothing jumps out ... Is that 27 seconds? Is your database normalized correctly? I had queries accessing multiple tables, with hundreds of thousands of records and I could throw queries against them and get almost instant responses, Oracle and SQL Servers using ODBC and ADO ... Can you run your queries in a Query Viewer in your database tool(s) and see how fast they run? That'll eliminate the code / application side of things. & maybe on the DB side you can setup views and stuff, if they aren't already. EDIT: I just saw you are using a query tool ... :( — tobeypeters, Sep 11 '18 at 15:37
@tobeypeters with sage 50 the database is closed so cannot do anything database wise am afriad. — david, Sep 11 '18 at 15:41
@david I just looked at it again. You're only accessing one table and it takes that long? Is there a way to find out if that table is a view and accessing multiple tables? Also, if there's any bad records with bad index fields ... BOY ... is that 27 seconds? You didn't say. If so, wow ... Does every other query against it, in your app and even others run like lightning? Your code looks pretty good. — tobeypeters, Sep 11 '18 at 15:53
@david It can also be purely Datanase Server performance. So many factors here. — tobeypeters, Sep 11 '18 at 16:00
That's a lot of large varchars. How many records are in the table? — MIKE, Sep 11 '18 at 16:32

score 1 · Answer 1 · answered Sep 11 '18 at 17:24

don't use {0}, {1} for embedding values in strings... ADD via Parameters

               String SQL = 
@"SELECT 
      ORDER_NUMBER, 
      ORDER_OR_QUOTE, 
      ANALYSIS_1,
      ACCOUNT_REF,
      ORDER_DATE,
      `NAME`, 
      COURIER_NUMBER,'
      OURIER_NAME,
      CUST_TEL_NUMBER,
      DESPATCH_DATE,
      ACCOUNT_REF,  
      DEL_NAME, 
      DEL_ADDRESS_1,
      DEL_ADDRESS_2,
      DEL_ADDRESS_3,
      DEL_ADDRESS_4,
      DEL_ADDRESS_5,
      INVOICE_NUMBER,
      ORDER_DATE,
      INVOICE_NUMBER_NUMERIC,
      CONTACT_NAME,
      CONSIGNMENT,
      NOTES_1,
      ITEMS_NET,
      ITEMS_GROSS,
      QUOTE_STATUS
   FROM 
      SALES_ORDER 
   WHERE 
          ORDER_DATE >= ?
      and ORDER_DATE <= ?
   ORDER BY
      ORDER_DATE DESC";

                using (var command = new OdbcCommand(SQL, connection))
                {
                   // assuming fields are actually date data types fields
                   command.Parameters.Add( "parmFromDate", fromD );
                   command.Parameters.Add( "parmToDate", toD );

The "?" in the query are place-holders for the parameter values which are handled by the ODBC process. The Parameters being added within the using() portion are added in the same ordinal position as their respective place-holder parts. I just assigned the parameter name to give context to whoever is looking at it after.

The query itself SHOULD be very quick depending on the date range you are pulling. Even added the SQL Order by descending order so it is pre-pulled down in the order you intended it too.

Query taking longer than expected odbc sage

1 Answers1