January 28, 2017 at 9:37 am
I have a while loop simple loop - where I am retrieving 10000 records.
When I ran in sql server - it has to give me 10000 select statements.. but its giving me out of mry exception.
When I retrieve the results in the text file at that time it went well .it retrieved the results fine.. the only problem with query window. when I try to retrieve the results in grid. --it errored out at 1964 rec - when I checked the message -
An error occurred while executing batch. Error message is: Error creating window handle.
BEGIN
declare @empid int
SET @empid = 10000
while @empid >= 0
begin
set nocount on
select * from emp
SET @empid = @empid - 1
END
END
January 28, 2017 at 9:47 am
Quick thought, looks like an SSMS error, not an SQL error. Do you have a lot of stuff running/plenty of ram/long time since last restart etc.?
😎
January 28, 2017 at 2:22 pm
This was removed by the editor as SPAM
January 28, 2017 at 2:38 pm
I am not selecting all columns from emp table
this is a function i am calling here to check the performance to see how it works for 10000 iterations.
select col1,col2,col3 from dbo.emp ('param1','param2''param3')
For 1000 records i am able to retreive with in 28 sec.. in the same way i want to see for 10000 iterations
January 28, 2017 at 4:40 pm
That's an error from Management Studio, not SQL Server itself.
To test your function, try inserting into a temp table instead. That way Management Studio doesn't have to try and create 10 000 results grids.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 28, 2017 at 5:57 pm
Here is my function we are already inserting into temp table inside the function.. this is taking some time to retrieve the results.. to see the actual timings I kept a loop to iterate 10000 rec to see the timings -- how to test this exactly by using loop?
CREATE FUNCTION dbo.emp
(@param1 nvarchar(100),
@param2 nvarchar(10),
@param2 varchar(8),
)
RETURNS @test-2 TABLE
(
col1 varchar(100),
col2 varchar(10),
col3 varchar(10)
)
AS
BEGIN
DECLARE
@Col1 varchar(15),
@Col2 varchar(35),
@Col3 varchar(20)
If (@Param1 = NULL)
BEGIN
SELECT
@Col1 = blabla,
@Col2 = blabla,
@col3 = bla END
ELSE IF (@Param2 != NULL)
BEGIN SELECT
@Col1 = blabla,
@Col2 = blabla,
@col3 = bla
END
Insert into @test-2
Select @Col1,
@Col2,
@Col3
Return
END
January 28, 2017 at 9:12 pm
mcfarlandparkway - Saturday, January 28, 2017 5:57 PMHere is my function we are already inserting into temp table inside the function.. this is taking some time to retrieve the results.. to see the actual timings I kept a loop to iterate 10000 rec to see the timings -- how to test this exactly by using loop?
CREATE FUNCTION dbo.emp
(@param1 nvarchar(100),
@param2 nvarchar(10),
@param2 varchar(8),
)
RETURNS @test-2 TABLE
(
col1 varchar(100),
col2 varchar(10),
col3 varchar(10)
)
AS
BEGIN
DECLARE
@Col1 varchar(15),
@Col2 varchar(35),
@Col3 varchar(20)If (@Param1 = NULL)
BEGIN
SELECT
@Col1 = blabla,
@Col2 = blabla,
@col3 = bla END
ELSE IF (@Param2 != NULL)
BEGIN SELECT
@Col1 = blabla,
@Col2 = blabla,
@col3 = bla
END
Insert into @test-2
Select @Col1,
@Col2,
@Col3Return
END
Maybe I'm missing something, but I'm just not getting your SQL. Your MTVF accepts 3 parameters, but doesn't use them. Another thing I noticed is that you're using = and != to check for NULL in @param1, but the matches won't be true. You need to check for NULL using IS NULL and IS NOT NULL instead of = and !=. Also, your loop is selecting from the MTVF 10000 times, but not passing any parameters. I don't see how the call to the function would work with a single test. BTW, temp tables are different in syntax than table variables and you're using a table variable. You'd likely get better performance by changing your COUNT directly from your source table. Or do you need the 10000 rows you're querying individually?
That said, the error is in your SSMS. Are you able to run simple queries against any table?
January 29, 2017 at 3:17 am
mcfarlandparkway - Saturday, January 28, 2017 5:57 PMHere is my function we are already inserting into temp table inside the function.. this is taking some time to retrieve the results.. to see the actual timings I kept a loop to iterate 10000 rec to see the timings -- how to test this exactly by using loop?
I said earlier. Insert the results of your function into a temp table, and then truncate the temp table inside the loop so that the insert is consistent in time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2017 at 3:18 am
Ed Wagner - Saturday, January 28, 2017 9:12 PMAre you able to run simple queries against any table?
He should be. The error's from SSMS trying to create too many results grids afaik. I've run into it when doing testing similar to this (though I tend to use GO <n> instead of a loop)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2017 at 9:57 am
GilaMonster - Sunday, January 29, 2017 3:18 AMEd Wagner - Saturday, January 28, 2017 9:12 PMAre you able to run simple queries against any table?He should be. The error's from SSMS trying to create too many results grids afaik. I've run into it when doing testing similar to this (though I tend to use GO <n> instead of a loop)
The result grids - thanks, Gail.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply