MSBI Learner (12/6/2011)
Yes – my motto is to improve the performance of my Stored Procedure. Yes – I can provide you the required information, would you mind to provide me your personal email ID as I don’t want to share the script in the thread due to some security issues.Hope you don’t mind.
Thanks !!
Sorry, no. Just obfuscate the data, so that it's similar to what you're using but not the same thus not violating any data protection laws.
For example, I use variations of the below script when I'm testing for performance
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
It creates 1,000,000 rows of pseudo-random data of different data-types.
Can you do something similar for the set-up of your issue? I know it means taking some time out to provide the scripts required, but I promise that there are some extremely talented people that frequent these forums so you will end up with a much more efficient query.