|
Sonia Gupta wrote: generally it is said that these are faster then the queries which we write directly into the code behind(asp.net code file).
It's not
Sonia Gupta wrote: i know stored procedure helps in preventing the SQL Query Injections.
NO. Parameterized queries prevents SQL Injection. Stored Procedures are not for this.
Sonia Gupta wrote: Now my Question is how stored procedure are faster then the query which we write directly, I mean what's the background scene behind this.
SP's are not faster that normal queries if it does only Insert/Delete/Update. Advantages I found with SP's are
1 - Clean separation of queries from code
2 - Easy maintainability
3 - Easy to write long transact SQL statements
4 - Security can be controlled
|
|
|
|
|
Sonia Gupta wrote: what's the background scene
Stored Procedures are checked for syntax errors, parsed, compiled and the execution plan is stored in the SQL Server's cache for subsequent execution. They are Precompiled collection of Transact-Sql Statements where as in the case inline-sql the statements are dynamically checked for syntax errors, then parsed, compiled and execution plan is prepared at the time of execution. These sqeuence of action happens every-time you execute a inline-sql where as in stored procedure it happens only once and the execution plan is Reused. This is the reason why Stored Procs are Faster than the inline-SQL's.
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
John Prabhu (J O N) wrote: These sqeuence of action happens every-time you execute a inline-sql where as in stored procedure it happens only once and the execution plan is Reused. This is the reason why Stored Procs are Faster than the inline-SQL's.
All the queries executed will be cached and re-used. It's not only for SP's.
John Prabhu (J O N) wrote: compiled and the execution plan is stored in the SQL Server's cache for subsequent execution. They are Precompiled collection of Transact-Sql Statements where as in the case inline-sql the statements are dynamically checked for syntax errors, then parsed, compiled and execution plan is prepared at the time of execution.
SQL server won't keep the execution plan in cache when SP's are created. It will check for the syntactical errors, but it's not precompiled. For both AD_HOC and SP's, execution plan would be prepared at the time of execution. And SQL server can retain all execution plans for all queries, not just SP's.
John Prabhu (J O N) wrote: his is the reason why Stored Procs are Faster than the inline-SQL's.
Still I don't think they are faster that inline-SQL's. It depends on the scenario where you use them. If you are using SP's for just Insert/Update/Delete, I don't think there would be drastic performance boost.
|
|
|
|
|
N a v a n e e t h wrote: Still I don't think they are faster that inline-SQL's.
I really don't understand what you are saying. it's you who taught me about the SQL Server performance when using Stored Procedures. Do you remember the article discussion we had few months ago. Check This[^]. You suggested me to read some Database Journals on Sql Server performance and went through some...this [^].
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
My apologies. Recently I found that my thinking was not correct. You may Check this[^].
|
|
|
|
|
Thanks for sharing.
- Regards -
JON
Life is not measured by the amount of breaths we take, but by the moments that take our breath away.
|
|
|
|
|
As Navaneeth says, SQL Server stored procedures are optimised no differently from other, ad-hoc queries. However, the resulting query plan is given more weight so it is more likely still to be in the cache when the procedure is next executed.
You must be a bit careful that you do not have massively divergent behaviour depending on different values for parameters. The reason for this is that SQL Server will use the previously compiled query plan regardless of the parameters supplied. If a query plan works well for some values of some parameters but not for others - perhaps an index which is very selective for some values but not for others, such as where a column mostly contains the same value but has a few exceptions - you can get better performance by asking SQL Server to recompile the query. Use WITH RECOMPILE with the EXEC statement to do this on a one-off basis, or specify WITH RECOMPILE in the CREATE PROCEDURE/ALTER PROCEDURE statement to force the plan to be recompiled every time.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Dear All,
I have a question regarding the usage of IDENTITY. Can any one answer me?
My Question is:
Whenever the primary key field is having an auto generated value i.e. IDENTITY. If any overflow occurs i.e. when the fields reaches its maximum value, what will happen?
Thanks in advance.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
obviously it will generate an error......... 
manoj Kumar Jha
"Learn to smile at every situation. See it as an opportunity to prove your strength and ability."
|
|
|
|
|
Practically, how can be get rid of this error?
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
Change the datatype of the ID field to one that holds values bigger than the one you have now.
|
|
|
|
|
Already my data type is big int.
My application is going to store voluminous transactions in the database.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
Then use GUID in SQL Server. by using the function newid()
If U Get Errors U Will Learn
If U Don't Get Errors U Have Learnt
|
|
|
|
|
BigInt and you think you're going to run out of IDs??
You can't possibly tell me that your table is going to hold more than 9,223,372,036,854,775,807 (time 2 for negative numbers) records. I'd like to see the storage drive array that's going to hold this database, not to mention the backup scheme you've got.
|
|
|
|
|
hi all.
i write a sp to create a temprory table with a select query and then with special column of myTemp table update another table.
i can access myTemp table after create it in a select query:
CREATE TEMPORARY TABLE
wgrade
select
//some code
FROM
`engage_applicant`
where
//some code
select wgrade.idApplicant from wgrade where ;
it works ok.
but when i use update query:
CREATE TEMPORARY TABLE
wgrade
select
//some code
FROM
`engage_applicant`
where
//some code
update tb2
set tb2.WrittenExamGrade=wgrade.WrittenExamGrade
where
tb2.idApplicant=wgrade.idApplicant;
i have this error:
Unknown column 'wgrade.idApplicant' in 'where clause'
Can any one help me?
sepel
|
|
|
|
|
hi,
i create the form1
Private Sub form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
AttachDB()
End Sub
and create module1
Module Module1
Public myConnection As New SqlConnection()
Public Sub AttachDB()
myConnection.ConnectionString = " Data Source=.\SQLEXPRESS;AttachDbFilename=C:\WindowsApplication1\SQLDB\ATM.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
myConnection.Open()
End Sub
End Module
and in run time stop the project and say there error in "AttachDB()
and error is "The type initializer for 'WindowsApplication1.Module1' threw an exception"
|
|
|
|
|
Have you figured out why this is happening?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
In couple of procedures we are passing xml formatted data and inserting into temporary tables using sp_xml_preparedocument. Is there any other approach without using sp_xml_preparedocument procedure we can insert data from xml formatted data. We are looking for better performance. I hope somebody can help on this.
Thanks in advance.
Thanks & Regards,
Kumar
|
|
|
|
|
What is wrong with the stored procedure?
Software_Guy_123 wrote: We are looking for better performance.
Might not be much else you can do. Maybe relook at how the xml data is being stored/fetched.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
hello frnds,
i have a table having 2 columns of type IMAGE i want to export the data and take it to my office,
i try with export to excel facility but failed.
what can do ????
Is there any other method to export image data...please tell
where i am wrong...?
thaknx in advance
-koolprasad2003 
Be a good listener...Because Opprtunity knoughts softly...N-Joy
|
|
|
|
|
You need to write some code to take the raw data and convert it into an image format. There are numerous samples around that show how to do this, but without knowing which languages you are familiar with, I can't refine this down for you.
|
|
|
|
|
I'm generally familiar with SQL Server 2005 and I know there's an IMAGE data type that I can use to store pictures in the db. Can MySQL do the same? I'll be heading to the book store tomorrow to pick up a book and read up on this, but any experience in this area, particularly including taking pictures that are saved on my local disk and storing them in a web-based MySQL db would be greatly appreciated. I've seen sample apps for SQL Server 2005 that do this, but not MySQL and get the impression it's not nearly as easy, but probably possible.
Thanks!!
modified on Monday, December 24, 2007 10:29:00 PM
|
|
|
|
|
Mike L. wrote: IMAGE data type that I can use to store pictures in the db. Can MySQL do the same?
Yes. I think it is the blob datatype.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thanks - I'll go get a book and see if I can find some examples.
|
|
|
|
|
I have a sample around here somewheres. I'll try to find it.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
