7 votes

Local Variables in Stored Procedures

post it From my blog post Yet Another Post About Local Variables In a stored procedure (and even in ad hoc queries or within dynamic SQL, like in the examples linked above), if you declare a variable ...
user avatar
  • 34.2k
7 votes
Accepted

SQL Server stored procedure taking too long sometimes even though indexes have been added with memory grant issues

superfund This query is pretty well a disaster, but if the reason it's sometimes slow is that it's waiting on memory (RESOURCE_SEMAPHORE) then the server you're on is likely underpowered for the ...
user avatar
  • 34.2k
4 votes

NONCLUSTERED index seek on mslid is slow

I'd be interested in the number of total rows you have in your Parts.ManufacturingData table and what the total runtime currently is for your query. Does the number of rows the execution plan says it'...
user avatar
  • 25.7k
4 votes
Accepted

SQL Server allow stored procedure chaining

I think what you're looking for is cross-database ownership chaining: Cross database ownership chaining is an extension of ownership chaining, except it does cross the database boundary. Please see ...
user avatar
  • 25.7k
4 votes

Temporarily disable SELECT for one table (during an update)

In a transaction ALTER the table. You will get an exclusive schema lock for the duration of the transaction. Requiring a transaction in SSIS may be enough, alternatively set RetainSameConnection on ...
user avatar
4 votes
Accepted

How to avoid deadlocking in a stored procedure with SERIALIZABLE isolation level, INSERTs, UPDATEs, and DELETEs

Ok, there is a lot to unpack in these procedures, so I'll do my best to lay it out in a comprehendible way. The way your queries are written, they're likely to contain many non-SARGABLE predications ...
user avatar
3 votes

Permissions for mysql 'SHOW CREATE PROCEDURE'?

From MySQL 8.0.20, instead of adding the global SELECT permissions (too broad), add the new SHOW_ROUTINE permissions: GRANT SHOW_ROUTINE ON *.* TO <USER>@<LOCATION>;
user avatar
3 votes

Dangers of running this stored procedure that kill blocking processes

Blocking doesn't "freeze up the db". It causes those who happen to be blocked to be ...blocked until the one holding the lock that causes the blocking situation to release that lock. That is ...
user avatar
3 votes
Accepted

Stored Procedure with WHERE .. IN array parameter

Use the ANY operator: UPDATE some_table SET touched = true WHERE id = ANY (id_list);
user avatar
3 votes
Accepted

Procedure not looping properly

In your code there is an error in: SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE `piggy_client_%` ORDER BY SCHEMA_NAME ASC; Replace backticks with string delimiter as ...
user avatar
  • 21.7k
2 votes

SELECT query slow even with nonclustered Index and a full-text index

The double-wild card action will not do you any favors ([varchar9] LIKE '%a%'). Because you're searching in the middle of the string, SQL Server is going to scan every row, and no index will help ...
user avatar
2 votes
Accepted

Postgresql - Is calling a stored procedure in a trigger possible?

No, a trigger needs to call a trigger function. But there is nothing that keeps you from writing a PL/pgSQL trigger function that CALLs the procedure of your liking.
user avatar
2 votes

Local Variables in Stored Procedures

As mentioned in existing comments, this is usually due to parameter sniffing when deciding what cached plan to use or when to produce a new one. It is often a cause (not always the cause, and this is ...
user avatar
2 votes
Accepted

Stored procedure on OVHCloud MySQL server fails with Unknown column '' in 'field list'

It seems that your server is using sql_mode=''. OVHCloud MySQL server has sql_mode ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES. ANSI activates ...
user avatar
  • 36
2 votes

NONCLUSTERED index seek on mslid is slow

Mybe you can try with a nonclustered columnstore index on the same fileds. This could possibly lead to a batch mode & compression so you can spare something in reading. microsoft doc example: ...
user avatar
  • 3,542
2 votes
Accepted

SQL Server - Best way to use case inside case

I have to use case inside case conditions No you don't. You are over-complicating your logic. And "Best" is never a good qualifier to include when your code has syntax errors. So let's start ...
user avatar
  • 315
2 votes
Accepted

How to save result from a Stored Procedure in a Linked Server to a variable or table?

If it is a single value as shown in the question, and you can modify the remote procedure, another option is to use an output parameter: -- On the remote server CREATE PROCEDURE dbo.ProcName @...
user avatar
  • 72.5k
2 votes

How to save result from a Stored Procedure in a Linked Server to a variable or table?

Your INSERT creates a local transaction, in which SQL Server is trying to enlist the remote procedure invocation using a distributed transaction, which is failing. So disable distributed transaction ...
user avatar
2 votes
Accepted

when update large table it take too much time to update

Your updated actual execution plan is also indicating you have no indexes on your extractreports.dbo.alldistSuppliersourceid table because it is currently using a Table Scan operation against it. It ...
user avatar
  • 25.7k
2 votes

Multiple Computed, Dependent Columns on INSERT in SQL Server?

For what it's worth, I think you already have the solution you're looking for: using PERSISTED computed columns. As a side note, you might want to ease up on your exchange rate datatype definition a ...
user avatar
  • 2,303
2 votes

how to update one column with trigger mysql

If you are saying that the row in inventory necessarily exists, the body of the Trigger is something like this: FOR EACH ROW UPDATE inventory SET pieces = pieces + 1 WHERE old.game ...
user avatar
  • 69.7k
2 votes

Keep procedure running even connection dies?

One solution could be to use the EVENT system: Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals. They ...
user avatar
  • 201
2 votes

SQL Query execution with IN statement

N'''2'',''3''' is a scalar value - as in a string: "'2','3'". SqlServer treats it as a singular string, when you use it as a parameter in your query. One solution could be to use WHERE IN (...
user avatar
  • 208
1 vote
Accepted

when make select to top 5 of rows from table it take too much time?

Your issue doesn’t really have anything directly to do to with the TOP 5. You have a GROUP BY and HAVING clause, both of which happen before the SELECT TOP. The GROUP BY may have to touch thousands of ...
user avatar
1 vote
Accepted

How to get partnumber that have at least have one source type Website and at least one stockid equal 1 Based on PartNumber?

One way would be using exists: select PartNumber,sourcetype,stockid from #stockyes s where exists ( select null from #stockyes s1 where s.PartNumber=s1.PartNumber ...
user avatar
  • 1,522
1 vote
Accepted

Trigger uses a procedure or a function?

Triggers have always been using functions, not procedures. Unfortunately, the terminology is fuzzy, many use "stored procedure" as umbrella term for both functions and procedures. Thus the ...
user avatar
1 vote
Accepted

how to display category name for c and x from table category without using self join?

Could be done using CROSS APPLY? SELECT d1.categoryname AS categoryc ,d2.categoryname AS categoryx FROM #category c CROSS APPLY #categorydetails AS d1 CROSS APPLY #categorydetails AS d2 WHERE ...
user avatar
  • 3,542
1 vote
Accepted

NONCLUSTERED index seek on mslid is slow

In addition to other right answers, you could also try an alternative method to improve the performance, that could be to create indexed view as below: CREATE VIEW parts.manufacturingdata_Indexed WITH ...
user avatar
1 vote

NONCLUSTERED index seek on mslid is slow

Your index seek is returning 32 million rows. You have a good plan, that’s just a lot of data to be reading. You could consider explicitly creating the destination table before your try to insert into ...
user avatar
1 vote
Accepted

Unknown column in field list MariaDB

|| is not for string concatenation in MySQL/MariaDB. Use the CONCAT(..., ...) function. Here is what MySQL says: mysql> select 'Total outstanding log inserted successfully ' || CHAR(...
user avatar
  • 69.7k

Only top scored, non community-wiki answers of a minimum length are eligible