1
\$\begingroup\$

I have these codes(.net framework 2.0):

    string commandText =
            "SELECT TOP(@maximumRows) * " +
            "FROM " +
            "( " +
                "SELECT *, ROW_NUMBER() OVER (ORDER BY " + sortExpression.Trim() + ") as rowNumber " +
                "FROM " +
                "( " +
                    "SELECT * FROM vw_RFQPartVendor " +
                    "WHERE Rec_date >= @requestDateFrom " +
                    "AND Rec_date <= @requestDateTo ";
    if (!string.IsNullOrEmpty(rfqNumber))
    {
        commandText += "AND Regno = @rfqNumber ";
    }
    if (!string.IsNullOrEmpty(partNumber))
    {
        commandText += "AND Partno = @partNumber ";
    }
    if (!string.IsNullOrEmpty(vendorCode))
    {
        commandText += "AND vendorCode = @vendorCode ";
    }
    commandText += ")as innerDerivedTable " +
")AS outerDerivedTable " +
"WHERE outerDerivedTable.rowNumber > @startRowIndex " +
"ORDER BY " + sortExpression.Trim();

The part WHERE Rec_date >= @requestDateFrom and Rec_date <= @requestDateTo is causing it to run slower, some noticeable seconds.

How can I optimize it to make it run faster?

\$\endgroup\$
  • 2
    \$\begingroup\$ Is rec_date indexed? \$\endgroup\$ – forsvarir Jun 30 '16 at 7:30
  • \$\begingroup\$ @forsvarir No and I went and added the index and hot damn, it actually make a difference. Not sure if it's psychological effects, haha. \$\endgroup\$ – Pop Jun 30 '16 at 7:57
  • 1
    \$\begingroup\$ index makes huge difference :) \$\endgroup\$ – MonsterMMORPG Jun 30 '16 at 13:13
  • \$\begingroup\$ Do you use C# 6? Or the compiler is as old as the framework? Otherwise you could make it much more readable. \$\endgroup\$ – Stefano d'Antonio Jul 1 '16 at 13:15
  • \$\begingroup\$ @Uno My guess is it's as old as the framework. \$\endgroup\$ – Pop Jul 4 '16 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.