SQL select on two tables takes too long
-
I have two tables on a database where I need to gather information for a report. I have built a select statement and run it through an Odbc.DataReader. Stepping through the debugger, once ExecuteReader is executed the program stalls, for a period of minutes. Is there anyway to make this select statement more efficient? The fields that start with INV are in the NINVREC table. The fields that start with MISCITEM are in the MISCITEM table. The matching keys are MISCITEM-SERIAL-NBR and INV-MK-SERIAL-NBR. The database is read only.
OdbcDataReader DbReader;
OdbcConnection DbConnection = new OdbcConnection("DSN=rsss");
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = "SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= '" + strEOMDate + "' AND MISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "') AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";try { DbConnection.Open(); DbReader = DbCommand.ExecuteReader(); **//stalls here** while(DbReader.Read())Jude
Is
MISCITEM-DATEa DateTime or a SmallDateTime, there can often be issues when querying on columns and the optimzer decides to convert a datetime in order not to lose percision. This may be even more possible because you have the lineMISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "'. I would try paramaterizing your query.DbCommand.CommandText =
"SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= @Date1 AND MISCITEM-DATE <= @Date2) AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";DbCommand.Paramaters.Add("@Date1", OdbcType.DateTime)
DbCommand.Paramaters["@Date1"].Value = strEOMDate;
DbCommand.Paramaters.Add("@Date2", OdbcType.DateTime)
DbCommand.Paramaters["@Date2"].Value = dtProcDate1;try
{
DbConnection.Open();DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
-
Is
MISCITEM-DATEa DateTime or a SmallDateTime, there can often be issues when querying on columns and the optimzer decides to convert a datetime in order not to lose percision. This may be even more possible because you have the lineMISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "'. I would try paramaterizing your query.DbCommand.CommandText =
"SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= @Date1 AND MISCITEM-DATE <= @Date2) AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";DbCommand.Paramaters.Add("@Date1", OdbcType.DateTime)
DbCommand.Paramaters["@Date1"].Value = strEOMDate;
DbCommand.Paramaters.Add("@Date2", OdbcType.DateTime)
DbCommand.Paramaters["@Date2"].Value = dtProcDate1;try
{
DbConnection.Open();DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
Thank you for the quick replies. The definition for the table states DATE TIME for that field. I did a q&d query on the field and it returned in the format of mm/dd/yyyy hh:mm:ss am, but all of the times were 12:00:00 am. I will try your solution and get back.
Jude
-
The query itself doesn't look like the problem. Without really knowing the DB it's tough to say, but you might consider putting indexes on serial number columns in both tables. You might also include the MiscItem-Date in the index you create on the MiscItem table.
Unfortunately it is a read only database...or should I say we only have read only access for reporting purposes.
Jude
-
Is
MISCITEM-DATEa DateTime or a SmallDateTime, there can often be issues when querying on columns and the optimzer decides to convert a datetime in order not to lose percision. This may be even more possible because you have the lineMISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "'. I would try paramaterizing your query.DbCommand.CommandText =
"SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= @Date1 AND MISCITEM-DATE <= @Date2) AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";DbCommand.Paramaters.Add("@Date1", OdbcType.DateTime)
DbCommand.Paramaters["@Date1"].Value = strEOMDate;
DbCommand.Paramaters.Add("@Date2", OdbcType.DateTime)
DbCommand.Paramaters["@Date2"].Value = dtProcDate1;try
{
DbConnection.Open();DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
Did not like. I am receiving the error "Required text is missing. : at 350 : Next Token '@' " Hmmm.....
Jude
-
Did not like. I am receiving the error "Required text is missing. : at 350 : Next Token '@' " Hmmm.....
Jude
Hmm sorry, looks like you have to specify your paramaters as a question mark in the actual query. I was going by my experience with SQLClient, try this
WHERE (MISCITEM-DATE >= ? AND MISCITEM-DATE <= ?) AND (INV-STATUS = 'S')" -
Hmm sorry, looks like you have to specify your paramaters as a question mark in the actual query. I was going by my experience with SQLClient, try this
WHERE (MISCITEM-DATE >= ? AND MISCITEM-DATE <= ?) AND (INV-STATUS = 'S')"True! Hmmm, but that seems to have broke it. It has been running in a console for about 10 minutes so far. One thing did change. The point of the stall is one step down in the program at the while(DbReader.Read()) statement.
Jude
-
Is
MISCITEM-DATEa DateTime or a SmallDateTime, there can often be issues when querying on columns and the optimzer decides to convert a datetime in order not to lose percision. This may be even more possible because you have the lineMISCITEM-DATE <= '" + dtProcDate1.ToString("yyyy-MM-dd") + "'. I would try paramaterizing your query.DbCommand.CommandText =
"SELECT MISCITEM-DATE, MISCITEM-SERIAL-NBR, INV-MK-SERIAL-NBR, MISCITEM-MODEL-NBR, MISCITEM-CUST-ACCT-NBR, MISCITEM-EXCHANGE-MODEL-NBR, " +
" INV-SELL-PRICE,INV-ORIGINAL-COST, MISCITEM-ACTUAL-COST, MISCITEM-SALESMAN-1, MISCITEM-TICKET-NBR, MISCITEM-STORE FROM MISCITEM " +
" INNER JOIN NINVREC ON MISCITEM-SERIAL-NBR=INV-MK-SERIAL-NBR " +
" WHERE (MISCITEM-DATE >= @Date1 AND MISCITEM-DATE <= @Date2) AND (INV-STATUS = 'S')" +
" GROUP BY MISCITEM-STORE, MISCITEM-DATE ORDER BY MISCITEM-STORE, MISCITEM-DATE";DbCommand.Paramaters.Add("@Date1", OdbcType.DateTime)
DbCommand.Paramaters["@Date1"].Value = strEOMDate;
DbCommand.Paramaters.Add("@Date2", OdbcType.DateTime)
DbCommand.Paramaters["@Date2"].Value = dtProcDate1;try
{
DbConnection.Open();DbReader = DbCommand.ExecuteReader();
while(DbReader.Read())
I edited the program and inserted a Timespan object. 27 minutes for the query! Ouch! It doesn't matter to me, as the report gets generated on a Sunday while nobody is working. But it just seems like it should not take this long.
Jude
-
I edited the program and inserted a Timespan object. 27 minutes for the query! Ouch! It doesn't matter to me, as the report gets generated on a Sunday while nobody is working. But it just seems like it should not take this long.
Jude
At some point you are going to need to get some optimisation done at the database level, indexes need to be created. That is why there are reporting databases and data warehouses. There is only so much you can do with query design. You might try and get them to allow you to use a schema then you could take snapshots of the data and manipulate it to get some performance.
Never underestimate the power of human stupidity RAH
-
True! Hmmm, but that seems to have broke it. It has been running in a console for about 10 minutes so far. One thing did change. The point of the stall is one step down in the program at the while(DbReader.Read()) statement.
Jude
Looks like it is an index problem then, it might be something you'll just have to live with.
-
At some point you are going to need to get some optimisation done at the database level, indexes need to be created. That is why there are reporting databases and data warehouses. There is only so much you can do with query design. You might try and get them to allow you to use a schema then you could take snapshots of the data and manipulate it to get some performance.
Never underestimate the power of human stupidity RAH
Sorry for th late reply. I received more documentation on the keys and indexes today. Here are the keys and indexes for the NINVREC table:
Table Indexes
Name Number of Fields
Key 1 2
Fields:
INV-MK-MODEL-NBR Ascending
INV-MK-SERIAL-NBR Ascending
Key 2 1
Fields:
INV-SERIAL-NBR-KEY Ascending
Key 3 1
Fields:
INV-PRIMARY-SERIAL-NBR Ascending
Key 4 1
Fields:
INV-DATE-SOLD Ascending
Key 5 1
Fields:
INV-LOCATION-REC-1 Ascending
Key 6 1
Fields:
INV-TICKET-NBR AscendingHere are the keys and indexes for the MISCITEM table:
Table Indexes
Name Number of Fields
Key 1 6
Fields:
MISCITEM-DATE Ascending
MISCITEM-HOUR Ascending
MISCITEM-MINUTE Ascending
MISCITEM-SECOND Ascending
MISCITEM-DUPLICATE-TIME Ascending
MISCITEM-STORE-KEY Ascending
Key 2 1
Fields:
MISCITEM-SALE-DATE Ascending
Key 3 3
Fields:
MISCITEM-CUST-ACCT-NBR Ascending
MISCITEM-TICKET-NBR Ascending
MISCITEM-TICKET-SEQ-NBR Ascending
Key 4 2
Fields:
MISCITEM-MODEL-NBR Ascending
MISCITEM-SERIAL-NBR Ascending
Key 5 2
Fields:
MISCITEM-EXCHANGE-MODEL-NBR Ascending
MISCITEM-EXCHANGE-SERIAL-NBR Ascending
Key 6 1
Fields:
MISCITEM-PENDING-SALE-DATE AscendingSo if I do a join on the INV-MK-MODEL-NBR-INV-MK-SERIAL-NBR and MISCITEM-MODEL-NBR-MISCITEM-SERIAL-NBR that should speed it up, correct?
Jude
-
At some point you are going to need to get some optimisation done at the database level, indexes need to be created. That is why there are reporting databases and data warehouses. There is only so much you can do with query design. You might try and get them to allow you to use a schema then you could take snapshots of the data and manipulate it to get some performance.
Never underestimate the power of human stupidity RAH
Yowza! That too the 27 minute query to less than 4 seconds! Thank you for all of your direction as I am not extremely fluent in SQL.
Jude