SQL query is taking 25 seconds in ASP.NET to load page

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

How can I boost the speed of page load to get data as early possible?

select B.bdregno, B.srno, B.bdbillno, B.bdfees as bdfees, B.bdqty as bdqty,
 B.bdrcode as bdrcode, B.bdtno as bdtno, B.bdvdate as bdvdate, T.TNAME as TNAME 
from dbo.[test] T full outer join 
 dbo.[billdet] B 
 on T.TCODE = B.bdtcode 
where" + qr + " bdregno='" + hdfregno.Value + "' 
order by bdvdate asc, srno

The query runs quickly in SQL Server, but in an ASP.NET application it takes approx 25 seconds to load the page.

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

Please tag the programming language that being used to load the page.

– Yogesh Sharma
Nov 16 '18 at 10:12

1

Step through your code and make sure, that it is indeed the execution of a simple select statement, which is causing the delay. If it works fine in SSMS, than there is no reason, why it would behave otherwise, while being executed from an application

– Marco
Nov 16 '18 at 10:18

What is the amount of data that is being transferred?

– johey
Nov 16 '18 at 10:20

A full outer join is seldomly used. Are you sure that is what you need?

– johey
Nov 16 '18 at 10:22

You should avoid to build your query using string concatenation. Use query parameters or Linq.

– johey
Nov 16 '18 at 10:24

|
show 3 more comments

How can I boost the speed of page load to get data as early possible?

select B.bdregno, B.srno, B.bdbillno, B.bdfees as bdfees, B.bdqty as bdqty,
 B.bdrcode as bdrcode, B.bdtno as bdtno, B.bdvdate as bdvdate, T.TNAME as TNAME 
from dbo.[test] T full outer join 
 dbo.[billdet] B 
 on T.TCODE = B.bdtcode 
where" + qr + " bdregno='" + hdfregno.Value + "' 
order by bdvdate asc, srno

The query runs quickly in SQL Server, but in an ASP.NET application it takes approx 25 seconds to load the page.

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

Please tag the programming language that being used to load the page.

– Yogesh Sharma
Nov 16 '18 at 10:12

1

Step through your code and make sure, that it is indeed the execution of a simple select statement, which is causing the delay. If it works fine in SSMS, than there is no reason, why it would behave otherwise, while being executed from an application

– Marco
Nov 16 '18 at 10:18

What is the amount of data that is being transferred?

– johey
Nov 16 '18 at 10:20

A full outer join is seldomly used. Are you sure that is what you need?

– johey
Nov 16 '18 at 10:22

You should avoid to build your query using string concatenation. Use query parameters or Linq.

– johey
Nov 16 '18 at 10:24

|
show 3 more comments

How can I boost the speed of page load to get data as early possible?

select B.bdregno, B.srno, B.bdbillno, B.bdfees as bdfees, B.bdqty as bdqty,
 B.bdrcode as bdrcode, B.bdtno as bdtno, B.bdvdate as bdvdate, T.TNAME as TNAME 
from dbo.[test] T full outer join 
 dbo.[billdet] B 
 on T.TCODE = B.bdtcode 
where" + qr + " bdregno='" + hdfregno.Value + "' 
order by bdvdate asc, srno

The query runs quickly in SQL Server, but in an ASP.NET application it takes approx 25 seconds to load the page.

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

How can I boost the speed of page load to get data as early possible?

select B.bdregno, B.srno, B.bdbillno, B.bdfees as bdfees, B.bdqty as bdqty,
 B.bdrcode as bdrcode, B.bdtno as bdtno, B.bdvdate as bdvdate, T.TNAME as TNAME 
from dbo.[test] T full outer join 
 dbo.[billdet] B 
 on T.TCODE = B.bdtcode 
where" + qr + " bdregno='" + hdfregno.Value + "' 
order by bdvdate asc, srno

The query runs quickly in SQL Server, but in an ASP.NET application it takes approx 25 seconds to load the page.

sql asp.net sql-server tsql stored-procedures

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

edited Nov 16 '18 at 13:35

Rahul Neekhra

6001627

asked Nov 16 '18 at 10:10

Prathamesh

314

asked Nov 16 '18 at 10:10

Prathamesh

314

asked Nov 16 '18 at 10:10

Prathamesh

314

Please tag the programming language that being used to load the page.

– Yogesh Sharma
Nov 16 '18 at 10:12

1

Step through your code and make sure, that it is indeed the execution of a simple select statement, which is causing the delay. If it works fine in SSMS, than there is no reason, why it would behave otherwise, while being executed from an application

– Marco
Nov 16 '18 at 10:18

What is the amount of data that is being transferred?

– johey
Nov 16 '18 at 10:20

A full outer join is seldomly used. Are you sure that is what you need?

– johey
Nov 16 '18 at 10:22

You should avoid to build your query using string concatenation. Use query parameters or Linq.

– johey
Nov 16 '18 at 10:24

|
show 3 more comments

Please tag the programming language that being used to load the page.

– Yogesh Sharma
Nov 16 '18 at 10:12

1

Step through your code and make sure, that it is indeed the execution of a simple select statement, which is causing the delay. If it works fine in SSMS, than there is no reason, why it would behave otherwise, while being executed from an application

– Marco
Nov 16 '18 at 10:18

What is the amount of data that is being transferred?

– johey
Nov 16 '18 at 10:20

A full outer join is seldomly used. Are you sure that is what you need?

– johey
Nov 16 '18 at 10:22

You should avoid to build your query using string concatenation. Use query parameters or Linq.

– johey
Nov 16 '18 at 10:24

Please tag the programming language that being used to load the page.

– Yogesh Sharma
Nov 16 '18 at 10:12

Step through your code and make sure, that it is indeed the execution of a simple select statement, which is causing the delay. If it works fine in SSMS, than there is no reason, why it would behave otherwise, while being executed from an application

– Marco
Nov 16 '18 at 10:18

What is the amount of data that is being transferred?

– johey
Nov 16 '18 at 10:20

A full outer join is seldomly used. Are you sure that is what you need?

– johey
Nov 16 '18 at 10:22

You should avoid to build your query using string concatenation. Use query parameters or Linq.

– johey
Nov 16 '18 at 10:24

|
show 3 more comments

1 Answer
1

active

oldest

votes

First, I doubt you really want a full outer join. I'm guessing a left join is sufficient:

select B.bdregno, B.srno, B.bdbillno, B.bdfees, B.bdqt,
 B.bdrcode, B.bdtno, B.bdvdate, T.TNAME 
from dbo.[billdet] B left join
 dbo.[test] T 
 on T.TCODE = B.bdtcode 
where b.bdregno = ? --'" + hdfregno.Value + "' 
order by b.bdvdate asc, srno

For this query, you want indexes on billdet(bdregno, bdtcode). The engine will still need to do the wort.

Note that you should be passing the value in as a parameter and not munging the query string.

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

add a comment |

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53335601%2fsql-query-is-taking-25-seconds-in-asp-net-to-load-page%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

First, I doubt you really want a full outer join. I'm guessing a left join is sufficient:

select B.bdregno, B.srno, B.bdbillno, B.bdfees, B.bdqt,
 B.bdrcode, B.bdtno, B.bdvdate, T.TNAME 
from dbo.[billdet] B left join
 dbo.[test] T 
 on T.TCODE = B.bdtcode 
where b.bdregno = ? --'" + hdfregno.Value + "' 
order by b.bdvdate asc, srno

For this query, you want indexes on billdet(bdregno, bdtcode). The engine will still need to do the wort.

Note that you should be passing the value in as a parameter and not munging the query string.

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

add a comment |

First, I doubt you really want a full outer join. I'm guessing a left join is sufficient:

select B.bdregno, B.srno, B.bdbillno, B.bdfees, B.bdqt,
 B.bdrcode, B.bdtno, B.bdvdate, T.TNAME 
from dbo.[billdet] B left join
 dbo.[test] T 
 on T.TCODE = B.bdtcode 
where b.bdregno = ? --'" + hdfregno.Value + "' 
order by b.bdvdate asc, srno

For this query, you want indexes on billdet(bdregno, bdtcode). The engine will still need to do the wort.

Note that you should be passing the value in as a parameter and not munging the query string.

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

add a comment |

First, I doubt you really want a full outer join. I'm guessing a left join is sufficient:

select B.bdregno, B.srno, B.bdbillno, B.bdfees, B.bdqt,
 B.bdrcode, B.bdtno, B.bdvdate, T.TNAME 
from dbo.[billdet] B left join
 dbo.[test] T 
 on T.TCODE = B.bdtcode 
where b.bdregno = ? --'" + hdfregno.Value + "' 
order by b.bdvdate asc, srno

For this query, you want indexes on billdet(bdregno, bdtcode). The engine will still need to do the wort.

Note that you should be passing the value in as a parameter and not munging the query string.

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

First, I doubt you really want a full outer join. I'm guessing a left join is sufficient:

select B.bdregno, B.srno, B.bdbillno, B.bdfees, B.bdqt,
 B.bdrcode, B.bdtno, B.bdvdate, T.TNAME 
from dbo.[billdet] B left join
 dbo.[test] T 
 on T.TCODE = B.bdtcode 
where b.bdregno = ? --'" + hdfregno.Value + "' 
order by b.bdvdate asc, srno

For this query, you want indexes on billdet(bdregno, bdtcode). The engine will still need to do the wort.

Note that you should be passing the value in as a parameter and not munging the query string.

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

answered Nov 16 '18 at 12:13

Gordon Linoff

796k37318423

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

add a comment |

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

thank u for ans i used it but now it takes 15 to 18 sec

– Prathamesh
Nov 17 '18 at 7:45

That is really interesting. If the full join version works faster, then I suspect you have a problem with the table statistics. You need to dive into the execution plans to see what the difference is between the full and left join versions.

– Gordon Linoff
Nov 17 '18 at 20:31

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Myujth