|
Hi, I have written a stored Procedure which fills data in to a Flat table, but all it needs to do is to check if a particular row is there if not insert the record to avoid duplication of the values.
And I am making this check against the same table that I am inserting. I am not sure what went wrong after this check the SP Performance has drastically dropped from 4 minutes to 20 minutes. Can anybody please help me how to improve performance of the SP in these situations.
Here is the check that I am doing on the table, all those columns that I am checking against are must, I have check this to avoid duplicate data in the table.
IF (@ChangeSetId IS NOT NULL)
BEGIN
IF NOT EXISTS (select top 1 * from TrackUpdatedColumnNamesFlatTable where
ChangeSetId=@ChangeSetId AND TransactionType=@TransactionType and TableName=@TableName
AND ColumnName=@ColumnName and PrimaryKeyColumnName=@PrimaryKeyColumnName and
PrimaryKeyValue=@PrimaryKeyValue)
begin
INSERT INTO TrackUpdatedColumnNamesFlatTable
(
ApplicationId
,ApplicationComponentId
,ApplicationName
,ApplicationFriendlyName
,SubApplicationName
,SubApplicationFriendlyName
,UserId
,SamAccountName
,FullName
,DefaultDisplayValue
,ChangeSetId
,TransactionType
,TableName
,ColumnName
,ColumnNameForDisplayValue
,FriendlyColumnName
,ColumnValueBefore
,ColumnDisplayValueBefore
,ColumnValueAfter
,ColumnDisplayValueAfter
,PrimaryKeyColumnName
,PrimaryKeyValue
,ImageBefore
,ImageAfter
,ChangedBy
,ChangedDate
,IsSoftDelete
)
select
@ApplicationId,
@ApplicationComponentId,
@ApplicationName,
@ApplicationFriendlyName,
@SubApplicationName,
@SubApplicationFriendlyName,
@UserId,
@SamAccountName,
@FullName,
@DefaultDisplayValue,
@ChangeSetId,
TransactionType,
TableName,
ColumnName,
@ColumnNameForDisplayValue,
@FriendlyColumnName,
ColumnValueBefore,
@ColumnDisplayValueBefore,
ColumnValueAfter,
@ColumnDisplayValueAfter,
PrimaryKeyColumnName,
PrimaryKeyValue,
ImageBefore,
ImageAfter,
@FullName,
@AuditDate,
IsSoftDelete
from #TrackUpdatedColumnNamesFlatTable where ID=@MinId
end
END
Unfortunately the SP is using the Dynamic Sql any thing to increase the Performance of the application helps a lot.
Any help a suggestion, a code snippet or a link anything helps me please. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 18-Apr-16 20:32pm.
|
|
|
|
|
You should be able to convert this to a single insert query with a correlated WHERE NOT EXISTS query, removing the loop entirely
Something like this:
INSERT INTO destination_table (
id
,data_col_1
,data_col_2
)
SELECT id
,data_col_1
,data_col_2
FROM #tempdata TD
WHERE NOT EXISTS (
SELECT 1
FROM destination_table
WHERE id = TD.id
)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Hi All.
I have a table in Microsoft SQL Database like that.
date,UserID,number_of_product, number_of_sale
Every day User sale number of product.
Now I Want to count consecutive for each user, each product, and each day the total number of sale in number of day (for example 7 days)
Thanks
|
|
|
|
|
|
The Data like that of course there is more ID
ID1,14/04/2016, 5
ID1,13/04/2016, 2
ID1,11/04/2016, 1
ID1,10/04/2016, 4
ID1,08/04/2016, 7
So the Result like that.
ID1, 14/04, 19 (19=5+2+1+4+7)
ID1, 13/04, 14 (2+1+4+7)
ID1, 11/04, 12 (1+4+7)
ID1, 10/04, 11 (4+7)
ID1, 08/04, 7
|
|
|
|
|
I gave you links to the command and option you need. Use those to construct a query (or multiple queries) to select the records you want.
|
|
|
|
|
Familiarise yourself with aggregate functions in SQL. GroupBy etc. If you learn them , what you are trying to do is simple for you.
|
|
|
|
|
I am using vb.net,Crystal and Sql. Some Time My Reports Timeout,
but Same Report execute. What to do?
|
|
|
|
|
Fix your query so the report doesn't time out.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
What do you mean "fix query"? and How to do It.
|
|
|
|
|
Change the query so that it runs more efficiently, or add or change indexes on the tables.
Seriously, how do you expect anyone here to be able to tell you what to do? We can't see you query or your data, and we can't access your database. What you've done is the equivalent of phoning a random garage, saying "My car's making a funny noise", and then hanging up and waiting for them to fix it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Dear Richard,
thank you for you advise
My Software is Running in a hospital with one Server and 25 Client. The query execute with in SQL In Less then 3 Second. while executing in .net some time it Time Out. I Want to Know What are the other reason of Query timeout.
with regard
|
|
|
|
|
You need to reply to the right message - I had no idea you'd posted this reply! 
There are lots of reasons why a query that runs fast in SQL Server Management Studio can be slow in .NET:
Slow in the Application, Fast in SSMS?[^]
The most common reason I've seen is the ARITHABORT setting - it's ON by default in SSMS, but OFF by default in .NET connections. Adding SET ARITHABORT ON to the start of your stored procedure often helps.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
How to display student record on screen by click on single button in android sqlite
|
|
|
|
|
Please do not crosspost; you already posted this question in the Android forum.
|
|
|
|
|
Hello,
First of all I'm not a SQL DBA.
I would like to test the AlwaysOn Availability Groups functionality of SQL server to validate or not if this solution is good for my company. Is it possible to test this for free? What do you suggest?
|
|
|
|
|
B413 wrote: I would like to test the AlwaysOn Availability Groups functionality of SQL server to validate or not if this solution is good for my company.
That would depend more if it actually does what you're buying it for. If it would not, it would fail as a product.
B413 wrote: Is it possible to test this for free?
There's a description to how you set it up on MSDN[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have a VS 2013 C# windows form application that binds a details view then a datagridview with an Access Database. I want to use SQL Light instead. There does not seem to be a simple procedure to click details view on the data source with SQL Light and drag the table onto the form as there is with Access. Can someone refer an article that explains how to accomplish this with C#?
|
|
|
|
|
|
Thank you for the links, Richard. I can't find what I am looking for. I have a windows form connected to an Access Database. Building the form was essentially drag and drop. I want to display a single record from a Sqlite database in an array of textboxes. VS will build all this when I select details view from the table, then drag the table onto the form. A data navigator is also installed. Then I drag a datagridview onto the form and the VS designer links everything together. So I don't really write any code. I installed the Sqlite toolbox, and I can add a datagridview easily. But not details. Since there seems to be nothing in VS to do this for Sqlite, can I simply change the connection string to the existing form that connects to Access?
|
|
|
|
|
BobbyStrain wrote: can I simply change the connection string to the existing form that connects to Access?
No idea, why not try it and see?
|
|
|
|
|
That's all it took. Of course, the project needs to add the library for SQLite. Now I am working to populate a combobox with unique values from a column in the database table.
Thanks.
|
|
|
|
|
Hi All,
I am trying to loop through a table and put the all the values into variables to further use logic on them, is there any way we can assign an Image value to a variable, when I am trying to do it, I am getting for the following error and when I am trying to convert the value of the image into nvarchar then also it is giving me errors that I can't convert. Can anybody please help me either by a code snippet, a link or even suggestion.
Thanks in advance.
The error it is giving is
The text, ntext, and image data types are invalid for local variables
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
The text, ntext and image types have been obsolete for many years:
Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Change your variable to varbinary(max). You should also change any columns using the text, ntext or image types to their supported equivalents.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
