i need better performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

i need better performance

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-09 : 09:35:45

hi all engineers, i hove your works goes well today

i want to design a website for implement an online exam.
we know that there is few ways to communicate with sql server inside a .net program
1. use select,update,insert,delete statements inside code file
2. define insert,update,delete,select statement inside a stored procedure and send parameters to it in the .net code file
3.using Linq to communicate with sql server
and ...

i wanna discuss about which one has better performance
thank u all

****<< I Love MTN.SH >>****

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 00:29:21

I will never go for step 1.

I will always use step 2.

I will give a try to step 3 in near future when LINQ becomes more flexible and stable.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-10 : 02:47:32

thanks for answer
i read in a book,linq for those queries which have where statement, load all data from server and then filters it on the memory. do u think this is good or bad??? i think it is very bad, but there is a question,is linq's benefits worth these overloads?

****<< I Love MTN.SH >>****

Kristen
Test

22859 Posts

Posted - 2010-09-10 : 03:31:18

Another way that I have seen people use, which has good performance, and is probably best suited to people who want (1) and have no knowledge of building Sprocs, is to build parametrised queries in their Application language and then use sp_ExecuteSQL to execute them.

It is essential that they are parametrised though!

SELECT * FROM MyTable WHERE Col1 = 1
and
SELECT * FROM MyTable WHERE Col1 = 2

are two different queries to SQL Server, and each will be separately evaluated and a new query plan made (**). What you want is for SQL to treat these as being the same query and to REUSE the query plan it has already made. So you need:

SELECT * FROM MyTable WHERE Col1 = @MyParameter

and then provide the value for @MyParameter separately.

sp_ExecuteSQL 'SELECT * FROM MyTable WHERE Col1 = @MyParameter', '@MyParameter int', @MyParameter = 1
and
sp_ExecuteSQL 'SELECT * FROM MyTable WHERE Col1 = @MyParameter', '@MyParameter int', @MyParameter = 2

is how you achieve that using sp_ExecuteSQL

I know very little about LINQ. However, my instinct is that when you introduce another level of abstraction, no matter how clever, you lose some control - and in this case that "control" is likely to be fine tuning control over performance. You will be tuning mechanically generated queries that make it harder for you to be easily able to see what columns need indexes etc. But my lack of knowledge on LINQ may mean that assumption is complete tosh!

(**) This is an oversimplification - SQL is smarter than that, but the general principle applies.

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2010-09-11 : 02:45:57

thanks Kristen
This is very exciting way to Execute Queries. but it is a little more complicate than other ways. but if you believe it has more performance for those sites have a lot of visitors, i will use it in my new web application
thanks

****<< I Love MTN.SH >>****

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources