MS SQL Server :: SQL Server 2008 :: How To Increase Performance Of Insertion SP In While Loop

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

Increase 'View' Performance

Apr 1, 2001

Hi,
I created a view on two huge tables. I tried to run a simple SELECT statement on this view and it took me several hours to obtain the result. How can I improve the performance of a view? The view should make use of the indexes built in both table, am I right? Thanks.

View 1 Replies View Related

Cursor Will Increase Performance Or Not

Jun 29, 2000

"Cursor provide row-by-row level processing and it will store the result sets in 'TEMPDB' database".

(Because of this) or (By using Cursor in Triggers or Stored Procedures) the performance will increase or performance will come down?. I am thankful if I get a good reason for this?

Srinivasan

View 3 Replies View Related

How To Increase SSIS Performance

Nov 3, 2006

Hello again,

I'll just throw my question: how could I increase SSIS-performance?

I have a really heavy job with thousands of records my base selection, then I perform some lookups (I replaced most of them by sql) and derived columns (again, I replaced as much as possible by sql). Finally, after a slowly changing dimension task, I do update/insert on a given table. Is there a trick to speed up lookups and inserts (something like manipulating the buffer sizes - just asking).
Fact is that I replaced a script task by pure sql-joins and gained 6 of the 12 hours this job took.

Any ideas?

Greets,
Tom

View 2 Replies View Related

How Much Can A Stored Procedure Increase Performance ???

Sep 25, 1998

Hi,

I am writing an ASP based application that creates a dynamic querry and then
executes it and displays results. I was thinking about writing a stored procedure to increase performance. How much can the SP help me boost querry responce time ???

Thanks for your time,
Robert

View 1 Replies View Related

How To Increase Performance Of A Stored Procedure

May 28, 2008

Hi,

Can any one give me an idea how can i increase performance of the stored procedure.
In SP many temporary tables are used.

Also i need a information from any one you that is there any tool to find out the performance of a query or SP etc.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 4 Replies View Related

Increase Performance In Query With Big Tables (milions Of Records)

Apr 4, 2008

Hello,

I have 3 tables (A, B, C) with milions of records (A ca 5 milions, B and C ca 10 milions).
I have created a join betwenn them

select some fields (A, B, C)
FROM
A as a
JOIN
B as B
on
a.a1 = b.a1
and
a.a2 = b.a2
JOIN
C as c
ON
b.b1 = c.b1
and
b.b2 = c.b2
Where fieldtime <= date/time

But it takes to much time: aftre 2 hours and half is still running.

Do you know how to increase the performance?

Thank

View 7 Replies View Related

Creating Indexes On Large Table To Increase Performance

Mar 5, 2008

Dear all,
I'm using SQL Server 2005 Standard Edetion.
I have the following stored procedure that is executed against two tables (RecrodedCalls) and (RecordedCallsTags)
The table RecordedCalls has more than 10000000 Records and RecordedCallsTags is about 7500000 Records
Now the lines marked in baby blue are dynamic (Dynamic where statement) that varies every time this stored procedure is executed, may it contains 7 columns in condetion statement or may it contains 10 columns, or 2 coulmns.....etc
Now I want to create non-clustered indexes on the columns used in the where statement, THE DTA suggests different indexing whenever the where statement changes.
So what is the right way to created indexes, to create one index on all the columns once, or to create separate indexes on each columns, sometimes the DTA suggests 5 columns together at one if I��m using 5 conditions, I can��t accumulate all the possible indexes hence the where statement always vary from situation to situation, below the SP:

CREATE TABLE #tempLookups (ID int identity(0,1),Code NVARCHAR(100),NameE NVARCHAR(500),NameA NVARCHAR(500))

CREATE TABLE #tempTable (ID int identity(0,1),TypesCount INT,CallsType NVARCHAR(50))

INSERT INTO #tempLookups SELECT Code, NameE, NameA FROM lookups WHERE [Type] = 'CALLTYPES' ORDER BY Ordering ASC

INSERT INTO #tempTable SELECT COUNT(DISTINCT(RecordedCalls.ID)) As TypesCount,RecordedCalls.CallType as CallsType

FROM RecordedCalls LEFT OUTER JOIN RecordedCallsTags ON RecordedCalls.ID = RecordedCallsTags.CallID

WHERE RecordedCalls.ID <= '9369907'

AND (RecordedCalls.CallDate BETWEEN cast ('01 Jan 1910 00:00:00:000' as datetime ) AND cast ( '01 Jan 2210 00:00:00:000' as datetime ))

AND (RecordedCalls.Duration BETWEEN 0 AND 1000000)

AND RecordedCalls.ChannelID NOT IN('62061','62062','62063','62064','64110','64111','64112','64113','64114','69860','69861','69862','69863','69866','69867','69868')

AND RecordedCalls.ServerID NOT IN('2')

AND RecordedCalls.AgentID NOT IN('1000010000')

AND (RecordedCallsTags.TagID is null OR RecordedCallsTags.TagID NOT IN('100','200'))

AND RecordedCalls.IsDeleted='false'

GROUP BY RecordedCalls.CallType

SELECT IsNull(#tempTable.TypesCount, 0) AS TypesCount, CASE('English')

WHEN 'Arabic' THEN #tempLookups.NameA

ELSE #tempLookups.NameE

END AS CallsType FROM

#tempTable RIGHT OUTER JOIN #tempLookups ON #tempTable.CallsType = #tempLookups.Code

DROP TABLE #tempLookups

DROP TABLE #tempTable

Thanks all,
Tayseer

Any suggestions how to create efficient indexes??!!

View 2 Replies View Related

SQL Server 2008 :: Use Top N For Select / Delete In A While Loop?

Jul 27, 2015

Can I safely use top n select/delete in a while loop? For example:

declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end

I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.

View 9 Replies View Related

SQL Server 2008 :: Loop To Insert 0s Into Table

Oct 22, 2015

I have a problem where I want to create a loop in my script to insert 0's into my table.

I have a temp table with a list of company codes.

SELECT CODE FROM ##SENData GROUP BY CODE

This produces the following;

CODE
00C
00D
00K
00M
00Q
00T
00V
00X (there are 110 of these codes)

I want to insert data into a different table in the following format.

+------+------------+-------+---------+
| CODE | Month | Value | Measure |
+------+------------+-------+---------+
| 00C | 01/09/2015 | 0 | HAR-01 |
| 00D | 01/09/2015 | 0 | HAR-01 |
| 00K | 01/09/2015 | 0 | HAR-01 |
| 00M | 01/09/2015 | 0 | HAR-01 |
| 00Q | 01/09/2015 | 0 | HAR-01 |
| 00T | 01/09/2015 | 0 | HAR-01 |
| 00V | 01/09/2015 | 0 | HAR-01 |
| 00X | 01/09/2015 | 0 | HAR-01 |
+------+------------+-------+---------+

The month will be set from a declared variable and the others will just be hard coded.

View 3 Replies View Related

SQL Server 2008 :: SSIS - For Each Loop Through Multiple Servers

Jan 30, 2015

I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?

View 1 Replies View Related

SQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?

Mar 20, 2015

If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End

I want to iterate this query using a loop as many as 5 times max.

View 3 Replies View Related

SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table

Sep 21, 2015

I have three tables:

"PaymentsLog"
"DatePeriod"
"PaidOrders"

As per below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](

[Code] ....

Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?

View 3 Replies View Related

SQL Server 2008 :: Performance Counter Prepare Values

Feb 11, 2015

Which values are best prepared value for given below memory objects

Memory
Parameter

Total memory=
SQL Cache Memory=
Lock Memory=
Optimizer Memory=
Connection Memory=
Granted WorkSpace Memory=
Memory Grants Pending=
Memory Grants Success=

Cache Details:

Cache Hit Ratio=
Cache Used/Min=
Cache Count=
Cache Pages=

Scheduled Jobs:

Job Status=
Run date & time=
Job Time=
Retries Attempted=

Need to know the above performance counters prepared values.

View 2 Replies View Related

Cannot See Sql Server 2008 Instance Databases In Performance Monitor

Apr 9, 2008

im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.

I can select The counter, but it does not let me select an instance database to monitor.

see image:

I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.

if i type LOCALHOST or 127.0.0.1 in the "Select counters from computer" text box i can see all databases under my sql 2005 instances, but not the ones under the 2008 instances. I have disabled all sql server 2005 services and can confirm none of the sql 2008 instance databases are showing up.

this is a huge problem, especially for when i start stress testing sql server 2008 in preperation for upgrading.

anyone any ideas on why i cannot see sql server 2008 instances in perfmon? I have tried from a remote pc also with the same results.

View 3 Replies View Related

SQL Server 2008 :: Estimate / Calculate Performance Overhead From Replication?

Jun 10, 2015

I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:

* Number of articles being replicated
* Types of articles being replicated
* Number of DML transactions that would result in delivery of replicated data

Any way to turn this into a meaningful metric?

View 0 Replies View Related

Performance Between 2005 And 2008

Apr 3, 2008

I was really impressed with the speed of sql server 2005, but when I upgraded to 2008, used the exact same database, and ran the exact same sql scripts, I noticed the performance was slower.
Is there a structural difference in 2008?

View 5 Replies View Related

How To Increase Tablespaces In Sql Server

Jul 14, 2004

Can someone tell me how to increase tablespaces in SQL Server?

Is the syntax the same as Oracle? Is there a wizard in Enterprise Manager?

Cheers

View 4 Replies View Related

How To Increase The SQL Server Memory

Dec 21, 2004

We are using SQL server 7.0, our users use to connect to SQL server remotely using Visual Basic Application. When all the users are connected it keeps on increasing the memory utilization and at 1,836,848 KB memory utilization it stops increasing the memory i.e memory utilization become stangnet. The actual amount of physical Memory is around 5GB but SQL server dont increase it after the treshhold level and the performance of the server get affected badly. Any one please suggest me the possible solution.

View 2 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

How To Increase Disk Space In Server

Aug 11, 2015

How to increase disk-space of database in sql server?

View 4 Replies View Related

SQL Server Memory Usage Continue To Increase

Dec 28, 2004

Hi,
On the last Sunday 26/12/04 on 20:00 I got on one of our applications in the customer site this error:
"There is insufficient system memory to run this query."

Details:

Computer: IBM XSERIES_345, dual CPU 2.8 Intel xeo, 1,047,952 KB ram, windows 2000 server sp4.
Disk space: 4395 MB, Virtual memory: initial size 1536 MB, Max size 3072 MB. Registry size: current 14 MB, Max 90 MB.

SQL Server 2000 SP 3, two production databases (1) Data size 100 MB, log size 15 MB (2) Data size 500 MB, log size 125 MB
All the memory options are configured as default i.e. dynamically configure for memory and not changes in any sp_configure options.

SQL server is the only major application running on the machine.

Facts:
On that evening the I've checked the Task Manager and found that SQL Server use 500 MB in Mem Usage and 1200 MB in VM size.
In the query analyzer I execute the select query which results the "�insufficient system memory�" error on both databases and on the one with more data it failed with this error, but on the other database it completed successfully.
I restart SQL Server service to solve it, and as for today 28/12/04 at 08:00 it use 590 MB in Mem Usage and 590 MB in VM size.

I have checked the system with Performance monitor and didn't find any problems I use the Memory: Pages/sec, Memory: Available Bytes, Physical Disk: % Disk Time, Processor: % Processor Time, SQL Server Buffer: Buffer Cache Hit Ratio.

We have 8 user connections for one of the database and 16 for the other one.
Our system runs with this configuration for 2 months, I backup the databases FULL, DIFF and LOG backup.
It's not real time system, and the load on the SQL is medium, but we use bad queries as update for all fields in one query (please don't ask�)

I know that SQL server is greedy with the memory and released memory only if other process needs it.

Any ideas why it happens?

Thanks,
Yaniv

View 2 Replies View Related

SQL Search :: How To Increase Server Buffer Size

Nov 6, 2015

I'm trying to query a table where in the data in a cell is 65KB and when i try to do a SELECT I am unable to get the entire data from the cell.

SELECT CAST(Xml_data as XML) from TableName where ID=100
Error Message: Msg 9448, Level 16, State 1, Line 1 XML parsing: line 241, character 76, well formed check: undeclared entity

View 4 Replies View Related

Foreach Loop Container - Foreach ADO Enumerator How Performance Compares To Use Of Cursors In Stored Procs

Apr 22, 2008

Hello

I have a question

How is foreach loop container - foreach ADO enumerator performace in SSIS package compares to use of cursors in stored procedures

Is there any articles comparing them

I understand a lot of factors can affect the performance, however what is expected performance for the foreach ADO enumerator loop for large dataset. What is Microsoft recommendation for that - recommended - not recommended (using large datasets - over million records)

Thank you
Arminr Bell

View 4 Replies View Related

Insertion Faild In Partitioned View In Sql Server 7.0

Dec 7, 2000

I have a problem while I try to insert data into a partioned view I am
getting the following error.

Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.

Any thoughts

USE pubs

CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO

ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO

ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO

View 2 Replies View Related

SQL Server 2012 :: By Pass Error While Insertion

Jan 29, 2014

I have a scenario in which if I do bulk insertion in a table and if any error occurs then insertion should not stopped and will proceed with insertion of remaining records.

View 2 Replies View Related

SQL Server 2014 :: Sudden Increase In Table Size?

May 5, 2015

I save Table size and recs. no every day. and check it some days.

...
insert into @t
exec sp_msforeachtable 'exec sp_spaceused ''?'''
...

But Today I saw sudden increase size in a table. about 128 MB in a day. (Average Growth fro this table was 4 or 5 MB in a day)This growth was for Only 4222 Records. While for more number of records (about 7000) in yesterday we had only 2 MB GRowth!

This Table information (Now):

sp_spaceused 'Table1'

Result:

name ---Rows --reserved --data

Table1--1021319--460328 KB --283104 KBI Try to gess The reason. I copy These new records to another table.But The result was more strange : on new table the size of these record was : < 1 MB I copied All records to another table . The size was : 148 MB (while this is 283 MB in my real database)

View 4 Replies View Related

SQL Server 2012 :: Increase In Usage Percentage Of All Columns

Aug 24, 2015

I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.

View 8 Replies View Related

Configure Data Source Insertion Into SQL Server 2005 Database - Express Editions

Sep 12, 2006

I am attempting to insert information from Visual Web Developer 2005 using either the Gridview or Datalist controls into a SQL Server 2005 database and get stuck when defining the custom statement.When I enter the text within the insert tab, the <next> button remains greyed out, preventing me from continuing to the next page.If I copy the same text into the select tab, then I can continue with the wizard, however this raises other problems which may or may not be related (multiple insertions of the data into the SQL Server database table - possibly due to postback functions). I would rather use insert to confirm that my second problem is not because I am using the wrong option.My question is:Should I be able to use the insert function within VWD express or is this only available within the standard/pro editions?

View 2 Replies View Related

How To Increase A Session Timeout For Report Creation Uing SQL Server 2005 Reporting Services.

Dec 31, 2007

Hi,
Does any know how to increase a session timeout for Report Creation uing SQL Server 2005 Reporting Services. Iam trying to export a report (to a .pdf) using SQL 05 reporting services. However, it seems that the the query is timing out coz of session-time out. Can anyone tell me how to increase this value ?

Thanks
Nik

View 11 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 11, 2008

Hello,

For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END

But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.

INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'

For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.

Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?

Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?

When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.

Thank you for your time. Any help greatly appreciated.

Thanks,
Kannan

View 1 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 5, 2008

Hello,

For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END

But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.

INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'

For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.

Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?

Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?

When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.

Thank you for your time. Any help greatly appreciated.

Thanks,
Kannan

View 5 Replies View Related