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
...
- 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 ...
- 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'...
- 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 ...
- 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 ...
- 40.9k
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 ...
- 3,218
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>;
- 131
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 ...
- 14.8k
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);
- 72.3k
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 ...
- 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 ...
- 343
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.
- 34k
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 ...
- 30.5k
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 ...
- 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:
...
- 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 ...
- 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
@...
- 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 ...
- 40.9k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 (...
- 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 ...
- 3,218
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
...
- 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 ...
- 155k
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 ...
- 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 ...
- 3,754
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 ...
- 3,218
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(...
- 69.7k
Only top scored, non community-wiki answers of a minimum length are eligible