CODEWITHSUNDEEP
sqlsql-serverexcelvba
ENGR024
ENGR024

Reputation: 327

Stored Procedure vs Direct Query in Excel

I have an excel file that will select roughly 1100 rows with 5 columns of data. Most columns are 5 digits long and are integers. I am using a macro to connect to a SQL server database and insert these rows into one maybe two tables. This is all its doing and then it closes the connection. So the user opens an excel file that has the rows, clicks a button and it executes the macro.

My question is, should the query be written in Excel since its simple and merely inserts the data into a few tables. Or is it more efficient calling a stored procedure and passing all of the values in the stored procedure and have it allocate where the values go in the different tables. When I mean efficient, i mean which is the quickest? I know this will probably take a few seconds to complete. I just feel going to a stored procedure is an extra point along the path that the data has to get to before it reaches the tables. Am I wrong? Any thoughts?

Upvotes: 1

Views: 545

Answers (2)

DeanOC
DeanOC

Reputation: 7282

In addition to @mark d.'s answer, another reason for using a stored procedure is security.

Your comment says that a customer is entering the data into Excel, so if you are putting direct SQL into your spreadsheet, then there is a risk that someone will open your spreadsheet and find out information about your database. But if you use a stored procedure then there is far less that can be learned.

Either way, make sure that you aren't hardcoding any connection string/account credentials into the spreadsheet.

Upvotes: 0

Mark D.
Mark D.

Reputation: 96

There are some advantages to using stored procedures in SQL Server. One is that SQL Server precompiles and saves the query execution plan, which increases performance. With your current method, SQL Server will generally need to generate the execution plan each time. Stored procedures can also reduce client/server network traffic.

So, even though it may seem like an extra point along the path, it actually can be faster.

Upvotes: 2

Related Questions