I have this SQL Server 2012 trivial stored procedure:
ASBEGINSETNOCOUNTONSELECT * FROM Company
and in a SSMS window, I execute this commands below to discover my SP is much slower than directly executing SELECT * from Company, even after the SP is run once to compile it.
-- create temp table so SSMS results output doesn't interfere too much with with query execution time SELECT * INTO #Company FROM Company WHERE 1=0
-- run the SP once to ensure that is is compiledINSERT #Company EXEC usp_SelectCompany
PRINT'Start Time: ' + cast(sysdatetime() asvarchar)
INSERT #Company EXEC usp_SelectCompany
PRINT'SP Time: ' + cast(sysdatetime() asvarchar)
INSERT #Company SELECT * FROM Company
PRINT'Select Time: ' + cast(sysdatetime() asvarchar)
It's better (1.0295934 seconds), but still slower than the select in the SSMS window.
The server has to do some things that can be skipped when being fed the statement directly, like looking up the access-rights to the sproc. So yes, compared to a simple query, it'd be a (small) bit slower.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
ExecuteReader performs about the same with either a select statement or a stored procedure. It appears to me that there is some penalty (lack of optimization?) when inserting the output from a stored procedure to a table that isn't present when inserting the output of a select statement.
SSMS produces a different "Actual Execution Plan" for the two, but I'm not skilled enough to understand what the differences mean. The execution plan breaks up the stored procedure into two queries, where the select statement is just one.
I am contemplating using this for the next version of 25 year old successfull software. We need to update various tables in a single transaction. This transaction needs to proceed in a step in which either All succeed, or None succeed. (resulting in a roll-back if any step failed) The database must deal with a high likelyhood that several terminals will at the same time try to write to the same entry in the same table. I handled this in MFC, with my own DB by giving each contentious item a serial-number, The first check for a modify-save would be if the serial number was still the same. if not, the item would be refused, and the transaction rolled back.
How does SQL handle this.
The word 'Serial Number' seems to link to some add somewhere. Nothing to do with me! Just, Don't click it on my behalf.
Thankfully that link has disappeared since. TAKE NOTE IF IT HAPPENS TO YOU. The term 'Serial Number' turned into a link to some mobile phone sellers. Its gone now.
Maybe I don't fully understand the problem, but SQL Server will handle "transactions" the same way. You declare the start of a transaction "Begin Transaction", do your stuff, then either "Commit" or "Rollback" the activity.
SQL server can automatically generate "serial" numbers for you. Look into the column data-type known as "Identity Column".
I have created a stored procedure which lists all customers that have 7 days left on their membership.
create proc spGetMemReminder
select users.fullname, membership.expiryDate from membership
inner join users on membership.uid=users.uid
where convert(varchar(10), expiryDate,105) = convert(varchar(10), (getdate() +7), 105)
I would like to insert this list into another table automatically. How do I achieve this? any suggestions appreciated. Thanks
Use an insert statement, that is not why I am posting a reply.
There are many ways to compare dates, comparing string (varchar) is probably the worst possible method. Do some research into the datetime object, you could have used datediff or dateadd neither of which require a convert.
Never underestimate the power of human stupidity
I have an SSIS package, which is trying to import data from a flat file in to a destination table in SQL. But the problem is not all columns in the destination table should come from flat file, there are few columns that should be filled from some other SQL Server source table.
Can we import data from a flat file and a table simultaneously into one table, is so how can we do that?
Please help me, its urgent. I am also searching but any help like code snippet, link or even a free advice is great.