locked
SqlDataSource very slow RRS feed

  • Question

  • User-1101714267 posted

    Hi all. I'm using a SqlDataSource component on a ASP.NET page. If I execute a sql query directly on database, results are available in 1 second or less.

    But same query, executed with SqlDataSource in the page returns nothing, as if it went in timeout. Database has a very large omount of data, but my query result 117 records only.

    Any suggestions?

    Friday, September 19, 2014 3:41 AM

Answers

  • User-1101714267 posted

    Hi andri745,

    For your issue, i suggest that you can set a breakpoint on your code  to see which part your code consume a lot of time.

    Check if it is related with adding many parameters ?

    Best Regards,

    Kevin Shen.

    Hi Kevin. Yes, problem was adding many parameters. I solved using a DataTable and a few parameters only.

    Bye,

    Andrea

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 23, 2014 2:33 AM

All replies

  • User-1716253493 posted

    Have you set gv enable paging?

    Do you have code behind?

    Friday, September 19, 2014 3:48 AM
  • User-1101714267 posted

    Yes, paging is enabled. This is code behind:

    <asp:SqlDataSource ID="SqlDSGridView" runat="server"
    					ConnectionString="<%$ ConnectionStrings:MyDb %>"                 
    					onselecting="SqlDSGridView_Selecting" 
                        DataSourceMode="DataSet">
    				</asp:SqlDataSource>
    
    
    
    				<asp:GridView ID="gvResult" runat="server" Visible="false" 
    					AutoGenerateColumns="false"
    					AllowSorting="True" 
    					AllowPaging="True" 
    					DataKeyNames="id" 
    					EmptyDataText="No docs"                  
    					EnableSortingAndPagingCallbacks="false" 
    					GridLines="None"  
    					ForeColor="#333333" 
    					CssClass="gv_result" 
    					ClientIDMode="Static" 
    					PageSize="15"
    					DataSourceID="SqlDSGridView" 
    					onrowdatabound="gvResult_RowDataBound">
    [...]

    Friday, September 19, 2014 6:13 AM
  • User-1360095595 posted

    What sql is executing? I don't see any sql in your datasource markup. 

    Friday, September 19, 2014 6:32 AM
  • User-1101714267 posted

    Sql query + parameters are dynamically added. That's the code execute when I press a button to search into database:

    private string InizializzaQueryGriglia() {
            StringBuilder sbResult = new StringBuilder();
            sbResult.Append("SELECT t1.id AS id, t1.Key01 as Nominativo, t1.Key02 As Indirizzo, t1.IsBollettino AS Bollettino, t1.Key13 as Citta, t1.Key06 as NrFattura, t1.Key05 as CustCode, ")
            .Append(" t1.Key14 as DataEmissione, t1.Key15 as DataConsegnaVettore, t1.Key12 as CAP,t1.Key20 as Provincia,r.Denominazione as Regione,tup.DataConsegna, tup.DataReso, ")
            .Append(" t1.Key19 as Delivery, t1.Key18 as Tariffa, tup.MotivazioneReso, tup.Esito, t1.Distanza ")
            .Append(" FROM ( IndexPbs AS t1 INNER JOIN Provincia p on (p.Sigla=t1.key20) Inner Join Regione r on (p.idRegione = r.id) ) ")
            .Append(" LEFT OUTER JOIN IndexUpdatePbs AS tup ON t1.key06 = tup.NrFattura ");
    
            Session.Add("queryExport", this.QueryExport());
            return sbResult.Append( "WHERE 1=0 ").ToString();
        }
    
    
    
        protected void SqlDSGridView_Selecting(object sender, SqlDataSourceSelectingEventArgs e) {
    
            e.Command.CommandTimeout = 2400000;
    
            string[] custCodes = txtCustcodes.Text.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
    
            List<string> lstCcFromFile = ReadUploadedFile();
    
            if(lstCcFromFile != null) {
                if(lstCcFromFile.Count > 0) {
                    custCodes = custCodes.Concat(lstCcFromFile).ToArray();
                }
            }
    
            e.Command.CommandText = this.InizializzaQueryGriglia();
            
            e.Command.Parameters.Clear();
    
            string realQuery = e.Command.CommandText;
    
    
            if(custCodes != null) {
                if(custCodes.Length > 0) {
    
                    e.Command.CommandText = e.Command.CommandText.Replace("1=0", "1=1");
    
                    e.Command.CommandText += " AND t1.Key05 IN (";
    
                    for(int i = 0; i < custCodes.Length; i++) {
                        SqlParameter par = new SqlParameter();
                        par.ParameterName = "@cc" + i.ToString();
                        par.DbType = DbType.String;
                        par.Value = custCodes[i].Trim();
    
                        e.Command.CommandText += par.ParameterName;
    
                        if(i != custCodes.Length - 1)
                            e.Command.CommandText += ", ";
    
                        e.Command.Parameters.Add(par);
                    }
    
                    e.Command.CommandText += ")";
                }
            }
    
            if(!String.IsNullOrEmpty(txtDataEmissioneDa.Text.Trim()) && !String.IsNullOrEmpty(txtDataEmissioneA.Text.Trim())) {
    
                SqlParameter par = new SqlParameter();
                par.ParameterName = "@EmissioneDa";
                par.DbType = DbType.DateTime;
                par.Value = Utilita.StringToDateTime(txtDataEmissioneDa.Text.Trim());
    
                e.Command.Parameters.Add(par);
    
                par = new SqlParameter();
                par.ParameterName = "@EmissioneA";
                par.DbType = DbType.DateTime;
                par.Value = Utilita.StringToDateTime(txtDataEmissioneA.Text.Trim());
    
                e.Command.Parameters.Add(par);
    
                e.Command.CommandText = e.Command.CommandText.Replace("1=0", "1=1");
                e.Command.CommandText += " AND (t1.Key14 BETWEEN @EmissioneDa AND @EmissioneA) ";
            }
            else {
                if(!String.IsNullOrEmpty(txtDataEmissioneDa.Text.Trim())) {
                    SqlParameter par = new SqlParameter();
                    par.ParameterName = "@EmissioneDa";
                    par.DbType = DbType.DateTime;
                    par.Value = Utilita.StringToDateTime(txtDataEmissioneDa.Text.Trim());
    
                    e.Command.Parameters.Add(par);
    
                    e.Command.CommandText = e.Command.CommandText.Replace("1=0", "1=1");
                    e.Command.CommandText += " AND (t1.Key14 >= @EmissioneDa) ";
                }
                else if(!String.IsNullOrEmpty(txtDataEmissioneA.Text.Trim())) {
                    SqlParameter par = new SqlParameter();
                    par.ParameterName = "@EmissioneA";
                    par.DbType = DbType.DateTime;
                    par.Value = Utilita.StringToDateTime(txtDataEmissioneA.Text.Trim());
    
                    e.Command.Parameters.Add(par);
    
                    e.Command.CommandText = e.Command.CommandText.Replace("1=0", "1=1");
                    e.Command.CommandText += " AND (t1.Key14 <= @EmissioneA) ";
                }
            }
    
            e.Command.CommandText += " ORDER BY t1.key14 DESC ";
            
            realQuery = DbManager.GetQueryConditions(e.Command.CommandText);
            Session.Add("query", realQuery);
    
    
            //collection di parametri per la query di esportazione
            List<SqlParameter> parsQueryExport = new List<SqlParameter>(e.Command.Parameters.Count);
            foreach(SqlParameter p in e.Command.Parameters)
                parsQueryExport.Add(new SqlParameter(p.ParameterName, p.Value));
    
            //query principale
            if(Session["queryExport"] == null)
                Session.Add("queryExport", this.QueryExport());
            else
                Session["queryExport"] = this.QueryExport();
    
            //SqlParams della query
            if(Session["queryParams"] == null)
                Session.Add("queryParams", parsQueryExport);
            else
                Session["queryParams"] = parsQueryExport;
    
            string queryConditions = DbManager.GetQueryConditions(e.Command.CommandText);
    
            //condizioni (where)
            if(Session["conditions"] == null)
                Session.Add("conditions", queryConditions);
            else
                Session["conditions"] = queryConditions;
    
            log.Info("Query eseguita: " + e.Command.CommandText);
        }
    

    It's complex because many parameters are added. In fact, if the query is executed without the SqlParameters, it is very much faster than now.

    Friday, September 19, 2014 8:21 AM
  • User1918509225 posted

    Hi andri745,

    For your issue, i suggest that you can set a breakpoint on your code  to see which part your code consume a lot of time.

    Check if it is related with adding many parameters ?

    Best Regards,

    Kevin Shen.

    Monday, September 22, 2014 9:56 PM
  • User-1101714267 posted

    Hi andri745,

    For your issue, i suggest that you can set a breakpoint on your code  to see which part your code consume a lot of time.

    Check if it is related with adding many parameters ?

    Best Regards,

    Kevin Shen.

    Hi Kevin. Yes, problem was adding many parameters. I solved using a DataTable and a few parameters only.

    Bye,

    Andrea

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 23, 2014 2:33 AM