I came across this very interesting question on Microsoft Forums where in the developer wanted to Create an SSIS package which Loops thought a set of tables and create Excel files on the fly and Load data into it.

There can be many ways to solve this including building a dot net custom component which creates excel sheet on the fly and then dynamically loads the data into it. Though I came across this cool SP that does this job really well and in very quick steps.

Here is how you can do this --

--        Enable SP_MAKEWEBTASK
3	exec sp_configure 'Web Assistant Procedures', 1 -- Configuration option 'Web Assistant Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install. RECONFIGURE
4	RECONFIGURE
5
6	--RUN THIS TO CREATE A FILE DUNAMICALLY AND LOAD DATA INTO IT
7
8	EXEC sp_makewebtask
9	@outputfile = 'd:\testing.xls',
10	@query = 'Select * from sys.tables',
11	@colheaders =1,
12	@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
13
14
15	--NOW WE KNOW HOW TO DYNAMICALLY CREATE A XLS FILE AND LOAD DATA FROM TABLES LETS TRY PUTTING THIS IN A SP AND RUNNING IT BY PARAMETERS
16
17	ALTER PROCEDURE CREATE_AND_LOAD_EXCEL
18
19	@ioutputfile       VARCHAR(2000)
20	,@itable            VARCHAR(MAX)
21	,@iresultstitle     VARCHAR(2000)
22	AS
23	declare @sql varchar(max),@excelfile varchar(max)
24	set @sql ='Select * from '+CHAR(9)+@itable
25	set @excelfile = 'd:\'+@ioutputfile
26	EXEC sp_makewebtask
27	@outputfile = @excelfile,
28	@query = @sql,
29	@colheaders =1,
30	@FixedFont=0,@lastupdated=0,@resultstitle=@iresultstitle
31
32	--EXECUTE THIS SP
33
34	EXEC CREATE_AND_LOAD_EXCEL
35	@ioutputfile = 'NewFile.xls',
36	@itable = 'sys.tables',
37	@iresultstitle='Testing details'

Now that we have seen that you can do this by passing table name and excel file name dynamically
this proc can be run with a execute sql task in a FOR EACH LOOP container where in you pass the
table where you want to load data from and name of excelfile you want to create dynamically.

Microsoft SQL Server 2005 Service Pack 3 (SP3) contains all the cumulative updates for SQL Server 2005, and fixes to issues that have been reported through our customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting.

Additionally, the following components have new or improved features.

Read here for more http://download.microsoft.com/download/0/e/5/0e5697be-028a-4da6-89a7-c18a9a1ac3c7/WhatsNewSQLServer2005SP3.htm

It is a very good idea that you a Batch Update to stop Lock Escalating to Tablock.

If you are using  SQL SERVER 2005 you can do UPDATE TOP N

1. Add a clolumn ISUpdated in the same table (or make another table which has all the empids and ISupdated column)

Code Snippet

update Top (@TopN) employees set payrate = payrate * 1.1 WHERE IS_UPDATED = 0

2. after this you need to update ISUpdatedFLag.

--IN any SQL version you can do this---------

If I was you I will do this

1. Create another table that has PrimaryKey of the table you are trying to Update , UpdateFLAG

Lets say you are updaing employee table then make a table emp_temp

(empid int,isupadted bit)

2. Now I can say

--Bring the first Batch--

insert into  emp_temp

select top 1000 empid,0 from Employee  A LEFT OUTER JOIN emp_temp B on A.empid = B.Empid

WHERE  B.Empid IS NULL  //Get those employeed which are not yet there in the emp_temptable

IF @@rowcount > 0 --- if there are new records to update

Begin

--Upate the first batch---

Update A

SET col1 = uuu,.........

FROM Employee  A Inner Join  emp_temp B On A.empid = B.EMPID

AND B.isupadted = 0

--Unflag after SuccessfulUpdate--

Update emp_temp  set isupadted  = 1 where isupadted  = 0

END

----------------------------------------------------------------------------------------------------------------------------

Another way if you have Datecolumn in the table (which I hope you would)

you can say

update  mytable set col1 = xxx................

where createdDate between Date1 and Date2 ---- again you can make batches of dates like

Date1 --- date2

date3----date4

Hope this helps

there is an article here. I have myself not tried it but you can give it a go

http://www.codeproject.com/KB/aspnet/BatchUpdate.aspx

When you are trying to perf tune some SQL query you must first clear the Cache and Buffers. SQL server caches the Plans for the query it has already executed and thus you will not get correct TIME performance counters.

In order to clear database engine Cache you can do this

DBCC FREEPROCCACHE

Here a code snippet from MSDN

USE AdventureWorks;
GO
SELECT * FROM Person.Address;
GO
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM Person.Address%';
GO

Here is the result set.

plan_handle                                         text

--------------------------------------------------  -----------------------------

0x060006001ECA270EC0215D05000000000000000000000000  SELECT * FROM Person.Address;

(1 row(s) affected)

Copy Code

-- Remove the specific plan from the cache.
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

Clearing all plans from the plan cache

The following example clears all elements from the plan cache. The WITH NO_INFOMSGS clause is specified to prevent the information message from being displayed.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

GO

DBCC DROPCLEANBUFFERS --> to test queries with cold buffer cache

more info :- http://msdn.microsoft.com/en-us/library/ms187762.aspx

DBCC FREESYSTEMCACHE('ALL')  -- Is same as DBCC CLEANPROCCACHE but this is only available in 2005

Summary :- You should always do the performance testing of the queries with a cold cache in order to get correct performance counters

There are times when all of us struggle to fin out the performance bottlenecks. I was playing around with PSSdiag and I realised how cool but underrated it is.

You can go here and download SQL Nexus and Play around with it.

http://www.codeplex.com/sqlnexus

Some time back I answered a post on forums about performance issues with linked server and few days back I was under the same situation.

There are couple of things that you can do

1.Create a stored procedure on the remote database and have it return a record set, being a subset of the remote tables, which is then loaded into a local temporary table. It can then be indexed as needed.

eg you have SP1 on you local server to get data. It needs data from linkedserver.table1 and table2

Create SP2 on the remote server to give you this data into a temp table . Index this table and let SP1 read from this table.

2. Try to optimise your SP by getting least data from the linked server but using proper filtering etc.

(even though it sounds obvious , as the linked server is normally not your server you don't know the data model well enough and sometimes due to improper filter criteria you bring in more data than you need)

3. if there is not improvement even by now... you might look at possibility of pulling some data to you server using SSIS and then use the data from your own server. ( This depends on the system that you worked for and it was possible for me to get half of the data that I needed by a package as it was not transactional data and so I did not have to JOIN to linked server tables to get them).