September 29, 2020 at 8:01 pm
Your concerned about the performance in .NET? Didn't you complain that the query you had crafted from Brian's input was very slow? Or were you able to resolve that?
My position is such that I should not argue about the performance in .NET, since that nothing I can claim expertise in. Then again, I think my SQL Server API for Perl performs something of the kind. I have never benchmarked it for performance, but it has never felt slow to me. But maybe that's because I've tried it with bigger volumes of data.
But you could try in a .NET forum to see if there are people who can give better advice on pivoting in .NET than I can do.
If you want to do it in the database, I repeat what I said in an earlier post:
As it happens, I also have some text about how to write dynamic pivot on my web site: http://www.sommarskog.se/dynamic_sql.html#pivot. That is part of a longer article on dynamic SQL, and you may have benefit to read it all. I discuss SQL injection in that article.
Did you look at my article?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 30, 2020 at 1:34 am
yes, i decide pivot in sql query. Now, problem is dynamic query as my post #3790843. The infomation in your website is very detailed
, but it's seen very complicated for me who have bad english ability.
October 7, 2020 at 2:46 am
hello, guys, i have some test with string_agg in sqlserver2017. It's seem to be best solution ever. With xml path quey run 30s, string_agg run 2s, oh my god. Perfect.
Now, i will upgrade sqlserver. So, how to upgrade 2008 express to 2017 express, intall a new instance sqlserver 2017, or direcly up from sqlserver2008.
And, have sql query syntax different between sql2008 and sql2017, or function which exists in 2008 and not exists in 2017 ?
Can i check objects in my database invalid in sqlserver2017 ?
October 7, 2020 at 6:37 am
Yes, string_agg is much more straightforward to use.
There are a few things that have been removed since SQL 2008, but not a lot. The COMPUTE BY clause, which I hope you never have heard of, has been dropped. They also discontinued an older syntax of RAISERROR without parentheses. There are a few more edge cases.
There are also changes to the optimizer which can backfire at times, but I say that you should go for it.
I would recommend that you install a new instance, unless this causes a problem for applications. The advantage is that the you have the old instance around and go back and check if you find something that does work as expected.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 8, 2020 at 1:32 am
hello, my storeproc, need to change for 2017 ?
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
query
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @ErrorMessage VARCHAR(2000), @Errornumber NVARCHAR(100)
SELECT @ErrorMessage = 'Error: ' + ERROR_MESSAGE()
RAISERROR(@ErrorMessage, 16, 1)
END CATCH
October 8, 2020 at 6:41 am
You don't need to, but you can change the CATCH handler to:
BEGIN CATCH
ROLLBACK
; THROW
END CATCH
; THROW reraises the error message exactly as it had been raised originally, and if there are multiple error message, you get them all. ;THROW also ensures that the execution is aborted.
But your current CATCH handler will continue to work, so you can keep it as it is, if you like.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 10, 2020 at 2:18 am
hello guys, is it ok if i write many jobs in a storeprocedure ? Like this:
ALTER PROCEDURE test
@kind NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON;
IF @kind = '1'
BEGIN
SELECT * FROM dbo.tbl1
END
IF @kind = '2'
BEGIN
SELECT * FROM dbo.tbl2
END
IF @kind = '3'
BEGIN
DELETE FROM dbo.tbl1 WHERE id = '01'
END
IF @kind = '4'
BEGIN
DELETE FROM dbo.tbl2 WHERE id = '01'
END
END
October 10, 2020 at 9:38 am
I can't see any relation to your original question here. You should probably have started a new thread.
There is nothing wrong with the pattern above. In fact, I would say that it is quite common, and I have used it myself many times.
The only thing to consider is that the client must be able to handle it. Some applications runs metadata queries to find out the shape of the result set before they call the actual stored procedure. They are likely to be confused by the pattern above. But as long as you write the application yourself, you have full control and can avoid any such problems.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 10, 2020 at 2:06 pm
hello, it's relation to myoriginal question here, because i have some test with stuff query in sql 2008.
CREATE PROCEDURE test
@kind NVARCHAR(10), @id NVARCHAR(10),
@startday date, @endday date
AS
BEGIN
SET NOCOUNT ON;
IF @kind = '1'
BEGIN
SELECT * FROM dbo.tbl1 where id = @id
END
IF @kind = '2'
BEGIN
my stuff query with @startday, @endday
END
END
Sometime, i execute proc with @kind = 2, it's run 2s (normal). but sometime it's run 30s or more or expire time, with same value of @startday and @endday.
I have some test, if i execute proc with @kind = 1 first, and run with @kind = 2 then i have problem, it's take slow. But i release cache plan of proc, and excecute with @kind = 2 first, or i execute with OPTION (RECOMPILE), it's run very fast.
I guess it's a problem of parameter sniffing or something else ? What do you think ?
October 10, 2020 at 4:54 pm
Yes, with this pattern you can get problems with parameter sniffing. Say that you first run the SP with @kind = 1 and you pass @startday and @endday as NULL. That second query will then be optimised for @startday and @endday as NULL and that may not be a good plan the typical values that you pass.
But note that this only applies if the query for @kind = 2 is directly in the stored procedure. If it is an inner scope - another stored procedure or dynamic SQL - there is no problem.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 11, 2020 at 2:05 am
hello, can you reconmend some way to solved that ?
If i split proc, that is nightmare about number of proc and manage them, one proc one action, usally a table have select, update, insert, delete => 4 proc per table.
October 11, 2020 at 9:35 am
So I gave some hints in my post. I also have an article on my web site where I discuss parameter sniffing in more detail, http://www.sommarskog.se/query-plan-mysteries.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply