Delete * Help??

Below is my Trigger. I want to DELETE ALL rows from EmployeeTemp but I receive a syntax error when doing a DELETE * FROM......
ANY IDEAS???

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Trigger trg_employees
On dbo.Employees
For Update
AS

Declare @.LastName varchar(255)
Declare @.FirstName varchar(255)
Declare @.Address varchar(255)
Declare @.City varchar(255)
Declare @.EmployeeID int

BEGIN
set @.LastName = (select LastName from Inserted)
set @.FirstName = (select FirstName from Inserted)
set @.Address = (select Address from Inserted)
set @.City = (select City from Inserted)
set @.EmployeeID = (select EmployeeID from Inserted)

Delete * EmployeeTEMP ----CAUSES SYNTAX ERROR BECAUSE OF '*'

INSERT INTO EmployeeTemp(EmployeeID, LastName, FirstName, Address, City)
Values(@.EmployeeID, @.LastName, @.FirstName, @.Address, @.City)
END
GO

EXEC master..xp_startmail
EXEC master..xp_sendmail
@.recipients ='grueneic@.drtel.com',
@.subject = 'Closed Service Order',
@.message = 'message test',
@.query = 'select EmployeeID, FirstName, LastName, Address, City from Northwind.dbo.EmployeeTemp'
EXEC master..xp_stopmail

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOIt would be

Delete * from TableName

but! if you TRUNCATE Table TableName
its _alot_ faster because it is a non-logged operation.|||Also be carefule in how you write your trigger. What happens if someone updates three rows instead of one row?

After you delete/truncate the table you could:

INSERT INTO EmployeeTemp(EmployeeID, LastName, FirstName, Address, City)
select EmployeeID, LastName, FirstName, Address, City
from inserted|||I agree with Paul Young. Your trigger is a bit unsafe.

But for the DELETE transaction, just write it that way:

DELETE TableName

Delet function not working on Gridview

I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

I using the products table in Northwind to learn this stuff.

The code that I used is below:

<%

@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource>

</div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>

</

body>

</

html>

Any help would be appreciated - thanx

The table has a foreign key constraint.

If a product is part of an order, you may not delete the product.

Otherwise, when someone tried to look at the order, they would get an error about a missing product.

|||

Thanx for your help - The reason I'm using the Products table in Northwind is, "I tried building a table based on my clients original data (his music shows itinerary) but it would not allow me to click the Checkbox in the Wizard - "Generate additional INSERT, UPDATE, and DELETE statements" when configuring the db

Can you tell me how I can remove the foreign key constraints (or) how can I duplicate the table, rename it and then remove all foreign key constraints

thanx

|||

Open up the Keys folder under the table.

The foreign keys have a prefix FK_. Right click one and select Modify.

When you built your original table, did you give it a primary key? If a table does not have a primary key, you will not be able to generate Insert, Delete and Update statements.,

|||

thank you so much - The primary key issue was the problem and now I have had no problems adding, deleting and modyfing the table. I am having a problem with the formatting of a datetime field. It keeps returning the date as well as the time (12:00 AM). Could you point me in the right direction to take care of this. Do I need to change the field or can I deal with it by formatting the column in the GridView?

thanx for all of your help

|||

In the GridView date column set the DataFormatString to {0:d} or {0:D} or {0:yyyy-MM-dd} etc.

And set the column's HtmlEncode property to false.

|||

Thanx Steve - Everything seems to be working fine now. Except,...

When I try to put this little app on my server (www.discountasp.net) - I get errors

Server Error in '/dave' Application.

---

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:Unrecognized attribute 'xmlns'.

Source Error:

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>Line 12: <connectionStrings/>

Source File:E:\web\nyguitarsne\htdocs\dave\web.config Line:10

I must be missing something crucial about deployment?

|||

PS - the site is sitting here:

www.caterdata.com/dave

|||

It's hard to say without seeing the whole file but it looks like you have a closing connectionStrings tag without an opening tag. The fix below may help (or you could delete both tags)

Line 8: \Windows\Microsoft.Net\Framework\v2.x\Config Line 9: -->Line 10: <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">Line 11: <appSettings/>

new: <connectionStrings>Line 12: <connectionStrings/>

|||

The reason I'm confused is that it is working fine on localhost.

Do I need to call the site Administrator and set something up that I'm not aware of?

|||

It looks like the web.config file on you local machine and the server are different.

Sometimes they should be. For instance, the datasource on a local machine may not exist on the server.

|||

Try making sure the server is setup top use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

|||

Try making sure the server is setup to use ASP.NET 2.0 and not ASP.NET 1.0

I have seen this cause weird problems as you are having.

David

sql

degrading performance on one table

Hello, we use one table in our SQL2k SP3a server for storing all sorts of
parameters. See the description below. It contains about 150.000 records.
What we see happening over the day is that the performance on accessing this
table deteriorates. When joins are made with other tables that use the
parameter table, they get slow too. It is very fast when SQL is freshly
started, but after a day or two spurious locks show up (I assume because of
the lack of response), and at somepoint I can't even do a select count (*)
anymore. Takes forever. There are no locks when I do this, I just wait
forever. Restarting SQL solved the problem, after that it is as fast as
ever!
This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
particular problem. Index has been defragged, to no avail.
Are we missing something obvious? Pointers as to where to look?
René
CREATE TABLE [dbo].[parameter] (
[entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[entity_id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
(
[entity_name],
[entity_id],
[name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOYou mentioned that you did reindex.
Do you do any deletes and updates on the table?
You mentioned no locks on the table when you run count(*). Is the server
performing bad for other tables at that time? Are there any open
transactions (run dbcc opentran). how about DBCC SHOWCONTIG (tablename)
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||Did you look at the query execution plan using one or more typical
"slowed-down" queries? Are the correct indexes being used? How about update
statistics? Is there tempdb issue 'cause it seems OK after service restart?
Any Perfmon findings on memory, processor, disk I/O counters?
Richard
"René" <rene.de.vries/atsign/kexdotnl> wrote in message
news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hello, we use one table in our SQL2k SP3a server for storing all sorts of
> parameters. See the description below. It contains about 150.000 records.
> What we see happening over the day is that the performance on accessing
this
> table deteriorates. When joins are made with other tables that use the
> parameter table, they get slow too. It is very fast when SQL is freshly
> started, but after a day or two spurious locks show up (I assume because
of
> the lack of response), and at somepoint I can't even do a select count (*)
> anymore. Takes forever. There are no locks when I do this, I just wait
> forever. Restarting SQL solved the problem, after that it is as fast as
> ever!
> This is REALLY puzzling us. We've run traces, etc- nothing to indicate any
> particular problem. Index has been defragged, to no avail.
> Are we missing something obvious? Pointers as to where to look?
> René
> CREATE TABLE [dbo].[parameter] (
> [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> [entity_id] [int] NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> (
> [entity_name],
> [entity_id],
> [name]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>|||I did have a look at temdb, and there was 98% unused space.. But in total it
is currently only
The query plans for a typical query looks ok, In fact, right after a
restart, that query is really fast - 0 second responses. The indexes look
okay, we experimented earlier with different indexes.
There is no change in load on CPU, memory of disk I/Owhen the performance
goed bad... CPU (2) are at maxed out at 50, sometimes peeking when a
full-text query is requested.
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uIE7$0sUDHA.360@.TK2MSFTNGP11.phx.gbl...
> Did you look at the query execution plan using one or more typical
> "slowed-down" queries? Are the correct indexes being used? How about
update
> statistics? Is there tempdb issue 'cause it seems OK after service
restart?
> Any Perfmon findings on memory, processor, disk I/O counters?
>
> Richard
> "René" <rene.de.vries/atsign/kexdotnl> wrote in message
> news:u3hHznsUDHA.1816@.TK2MSFTNGP09.phx.gbl...
> > Hello, we use one table in our SQL2k SP3a server for storing all sorts
of
> > parameters. See the description below. It contains about 150.000
records.
> >
> > What we see happening over the day is that the performance on accessing
> this
> > table deteriorates. When joins are made with other tables that use the
> > parameter table, they get slow too. It is very fast when SQL is freshly
> > started, but after a day or two spurious locks show up (I assume because
> of
> > the lack of response), and at somepoint I can't even do a select count
(*)
> > anymore. Takes forever. There are no locks when I do this, I just wait
> > forever. Restarting SQL solved the problem, after that it is as fast as
> > ever!
> >
> > This is REALLY puzzling us. We've run traces, etc- nothing to indicate
any
> > particular problem. Index has been defragged, to no avail.
> >
> > Are we missing something obvious? Pointers as to where to look?
> >
> > René
> >
> > CREATE TABLE [dbo].[parameter] (
> > [entity_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> > ,
> > [entity_id] [int] NOT NULL ,
> > [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [value] [varchar] (4096) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[parameter] WITH NOCHECK ADD
> > CONSTRAINT [PK_parameter] PRIMARY KEY CLUSTERED
> > (
> > [entity_name],
> > [entity_id],
> > [name]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> >
>

Defrag and Index

I am by no means an SQL expert, I need to figure out if doing a nightly
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Defining code for better performance

Hi below is a SP that I am using to build a temp table and send back
paginated results to ASP. The prcedure itself works fine and does the
job although a little slow, I am sure the is a way that I can further
define it so that it runs faster but SQL isn't my strong point and I was
wondering if someone could help out.
Thanks in advance
Peter
CREATE PROCEDURE dbo.cnms_employee_page
(
@.Page INT,
@.RecsPerPage INT,
@.pagenumbers INT = NULL OUTPUT
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
myID INT IDENTITY,
row_id INT,
NETWORK_ID VARCHAR(3),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
LEV1_ID VARCHAR(10),
LEV2_ID VARCHAR(10),
LEV3_ID VARCHAR(10),
EMP_LAST_NAME VARCHAR(25),
EMP_FIRST_NAME VARCHAR(15),
EMP_TITLE VARCHAR(30),
CUSTOMER_ID VARCHAR(20),
LOCATION_ID VARCHAR(10),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1),
NETWORK_NAME VARCHAR(40),
LEV1_NAME VARCHAR(20),
LEV2_NAME VARCHAR(20),
LEV3_NAME VARCHAR(20),
CORP_NAME VARCHAR(40)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
FROM Employee INNER JOIN
Network ON Employee.NETWORK_ID = Network.NETWORK_ID INNER JOIN
Corporation ON Employee.NETWORK_ID = Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
JOIN
CORPLVL1 ON Employee.NETWORK_ID = CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
Corplvl2 ON Employee.NETWORK_ID = Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
Employee.LEV1_ID = Corplvl2.LEV1_ID AND
Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
Corplvl3 ON Employee.NETWORK_ID = Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
Employee.LEV1_ID = Corplvl3.LEV1_ID AND
Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID = Corplvl3.LEV3_ID
GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
ORDER BY Employee.row_id DESC
SELECT pagenumbers = COUNT(row_id) FROM #TempItems
-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords = (
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.myID >= @.LastRec
)
FROM #TempItems
WHERE myID > @.FirstRec AND myID < @.LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Looking through your SQL, there are a lot of joins.
It might be worth executing the SELECT statement within query analyzer &
checking out the execution plan, as this might give you some insight as to
whether there are any bottlenecks in the statement.
If this is run frequently enough, it might also be worth running the index
tuning wizard against this statement to see if it has any suggestions...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Peter
What is amount of data you are inserting into the temp table?
I'd create clustered index on myID column (very useful with range
searching )
Try to a add with recompile to stored procedure and look at query optimyzer
output whether or not there are some differences.
Try to avoid using = NULL with parameters instead use =0
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uCRzU2N4DHA.3104@.TK2MSFTNGP11.phx.gbl...
> Hi below is a SP that I am using to build a temp table and send back
> paginated results to ASP. The prcedure itself works fine and does the
> job although a little slow, I am sure the is a way that I can further
> define it so that it runs faster but SQL isn't my strong point and I was
> wondering if someone could help out.
> Thanks in advance
> Peter
>
> CREATE PROCEDURE dbo.cnms_employee_page
> (
> @.Page INT,
> @.RecsPerPage INT,
> @.pagenumbers INT = NULL OUTPUT
> )
> AS
> SET NOCOUNT ON
> --Create a temporary table
> CREATE TABLE #TempItems
> (
> myID INT IDENTITY,
> row_id INT,
> NETWORK_ID VARCHAR(3),
> CORP_ID VARCHAR(10),
> EMP_ID VARCHAR(10),
> LEV1_ID VARCHAR(10),
> LEV2_ID VARCHAR(10),
> LEV3_ID VARCHAR(10),
> EMP_LAST_NAME VARCHAR(25),
> EMP_FIRST_NAME VARCHAR(15),
> EMP_TITLE VARCHAR(30),
> CUSTOMER_ID VARCHAR(20),
> LOCATION_ID VARCHAR(10),
> START_DATE datetime,
> END_DATE datetime,
> username VARCHAR(50),
> action_date datetime,
> user_action CHAR(1),
> NETWORK_NAME VARCHAR(40),
> LEV1_NAME VARCHAR(20),
> LEV2_NAME VARCHAR(20),
> LEV3_NAME VARCHAR(20),
> CORP_NAME VARCHAR(40)
>
> )
>
> -- Insert the rows from tblItems into the temp. table
>
> INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
> LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
> LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
> NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
> SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> FROM Employee INNER JOIN
> Network ON Employee.NETWORK_ID => Network.NETWORK_ID INNER JOIN
> Corporation ON Employee.NETWORK_ID => Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
> JOIN
> CORPLVL1 ON Employee.NETWORK_ID => CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
> Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
> Corplvl2 ON Employee.NETWORK_ID => Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
> Employee.LEV1_ID = Corplvl2.LEV1_ID AND
> Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
> Corplvl3 ON Employee.NETWORK_ID => Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
> Employee.LEV1_ID = Corplvl3.LEV1_ID AND
> Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID => Corplvl3.LEV3_ID
> GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> ORDER BY Employee.row_id DESC
> SELECT pagenumbers = COUNT(row_id) FROM #TempItems
>
> -- Find out the first and last record we want
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
> SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT *,
> MoreRecords => (
> SELECT COUNT(*)
> FROM #TempItems TI
> WHERE TI.myID >= @.LastRec
> )
> FROM #TempItems
> WHERE myID > @.FirstRec AND myID < @.LastRec
>
> -- Turn NOCOUNT back OFF
> SET NOCOUNT OFF
> GO
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You might also be better simplifying the insert. You should not need to
insert all of the columns you require to be output, just the key ones
(hopefully a single column). You could then join this to your source tables,
& save a potentially huge amount of insert activity...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Defining code for better performance

Hi below is a SP that I am using to build a temp table and send back
paginated results to ASP. The prcedure itself works fine and does the
job although a little slow, I am sure the is a way that I can further
define it so that it runs faster but SQL isn't my strong point and I was
wondering if someone could help out.
Thanks in advance
Peter
CREATE PROCEDURE dbo.cnms_employee_page
(
@.Page INT,
@.RecsPerPage INT,
@.pagenumbers INT = NULL OUTPUT
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
myID INT IDENTITY,
row_id INT,
NETWORK_ID VARCHAR(3),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
LEV1_ID VARCHAR(10),
LEV2_ID VARCHAR(10),
LEV3_ID VARCHAR(10),
EMP_LAST_NAME VARCHAR(25),
EMP_FIRST_NAME VARCHAR(15),
EMP_TITLE VARCHAR(30),
CUSTOMER_ID VARCHAR(20),
LOCATION_ID VARCHAR(10),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1),
NETWORK_NAME VARCHAR(40),
LEV1_NAME VARCHAR(20),
LEV2_NAME VARCHAR(20),
LEV3_NAME VARCHAR(20),
CORP_NAME VARCHAR(40)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
FROM Employee INNER JOIN
Network ON Employee.NETWORK_ID =
Network.NETWORK_ID INNER JOIN
Corporation ON Employee.NETWORK_ID =
Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
JOIN
CORPLVL1 ON Employee.NETWORK_ID =
CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
Corplvl2 ON Employee.NETWORK_ID =
Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
Employee.LEV1_ID = Corplvl2.LEV1_ID AND
Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
Corplvl3 ON Employee.NETWORK_ID =
Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
Employee.LEV1_ID = Corplvl3.LEV1_ID AND
Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID =
Corplvl3.LEV3_ID
GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
ORDER BY Employee.row_id DESC
SELECT pagenumbers = COUNT(row_id) FROM #TempItems
-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.myID >= @.LastRec
)
FROM #TempItems
WHERE myID > @.FirstRec AND myID < @.LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Looking through your SQL, there are a lot of joins.
It might be worth executing the SELECT statement within query analyzer &
checking out the execution plan, as this might give you some insight as to
whether there are any bottlenecks in the statement.
If this is run frequently enough, it might also be worth running the index
tuning wizard against this statement to see if it has any suggestions...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Peter
What is amount of data you are inserting into the temp table?
I'd create clustered index on myID column (very useful with range
searching )
Try to a add with recompile to stored procedure and look at query optimyzer
output whether or not there are some differences.
Try to avoid using = NULL with parameters instead use =0
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uCRzU2N4DHA.3104@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi below is a SP that I am using to build a temp table and send back
> paginated results to ASP. The prcedure itself works fine and does the
> job although a little slow, I am sure the is a way that I can further
> define it so that it runs faster but SQL isn't my strong point and I was
> wondering if someone could help out.
> Thanks in advance
> Peter
>
> CREATE PROCEDURE dbo.cnms_employee_page
> (
> @.Page INT,
> @.RecsPerPage INT,
> @.pagenumbers INT = NULL OUTPUT
> )
> AS
> SET NOCOUNT ON
> --Create a temporary table
> CREATE TABLE #TempItems
> (
> myID INT IDENTITY,
> row_id INT,
> NETWORK_ID VARCHAR(3),
> CORP_ID VARCHAR(10),
> EMP_ID VARCHAR(10),
> LEV1_ID VARCHAR(10),
> LEV2_ID VARCHAR(10),
> LEV3_ID VARCHAR(10),
> EMP_LAST_NAME VARCHAR(25),
> EMP_FIRST_NAME VARCHAR(15),
> EMP_TITLE VARCHAR(30),
> CUSTOMER_ID VARCHAR(20),
> LOCATION_ID VARCHAR(10),
> START_DATE datetime,
> END_DATE datetime,
> username VARCHAR(50),
> action_date datetime,
> user_action CHAR(1),
> NETWORK_NAME VARCHAR(40),
> LEV1_NAME VARCHAR(20),
> LEV2_NAME VARCHAR(20),
> LEV3_NAME VARCHAR(20),
> CORP_NAME VARCHAR(40)
>
> )
>
> -- Insert the rows from tblItems into the temp. table
>
> INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
> LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
> LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
> NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
> SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> FROM Employee INNER JOIN
> Network ON Employee.NETWORK_ID =
> Network.NETWORK_ID INNER JOIN
> Corporation ON Employee.NETWORK_ID =
> Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
> JOIN
> CORPLVL1 ON Employee.NETWORK_ID =
> CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
> Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
> Corplvl2 ON Employee.NETWORK_ID =
> Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
> Employee.LEV1_ID = Corplvl2.LEV1_ID AND
> Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
> Corplvl3 ON Employee.NETWORK_ID =
> Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
> Employee.LEV1_ID = Corplvl3.LEV1_ID AND
> Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID =
> Corplvl3.LEV3_ID
> GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> ORDER BY Employee.row_id DESC
> SELECT pagenumbers = COUNT(row_id) FROM #TempItems
>
> -- Find out the first and last record we want
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
> SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT *,
> MoreRecords =
> (
> SELECT COUNT(*)
> FROM #TempItems TI
> WHERE TI.myID >= @.LastRec
> )
> FROM #TempItems
> WHERE myID > @.FirstRec AND myID < @.LastRec
>
> -- Turn NOCOUNT back OFF
> SET NOCOUNT OFF
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

|||You might also be better simplifying the insert. You should not need to
insert all of the columns you require to be output, just the key ones
(hopefully a single column). You could then join this to your source tables,
& save a potentially huge amount of insert activity...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Thanks for your suggestions guys, I did a test as James suggested
firstly as the select stood, I then ran another test just requesting the
row_id, I think the best thing to do would be to retrieve the just the
row_id as James suggested and then use multiple selects to return the
other values removing the joins which seemed to be causing the heavy
cost.
If you have any better approaches then please let me know
Many thanks again
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

default values are passing in the report??

hello guys i am passing the below url in the web browser

http://pc-oreddy/crreports/Final%20COOL%20report1.rpt?user0=crystal&password0=crystal&promptex-Node="www.xpedite.com"&promptex-Manager="All"&promptex-Date=["Datetime(2005,02,18,12,00,00)"-"Datetime(2005,03,10,12,00,00)"]

By default a report is opening with values( i mean the report is not based on the parameter values i am entering in the url). once i click the refresh button in the report, then it is directing me to a page where i have these

The report you requested requires further information.
When you are done, click View Report.

Crystal Parameter Field(s)
You can provide a single range of values for this parameter. Choose or enter a lower and an upper limit to describe the range of values you want to include. Date
Enter the date for which you wish to see the events

Range of values between:
DateTime (2005,02,18,12,00,00) Datetime(2005,02,18,12,00,00)

and
DateTime (2005,03,10,12,00,00) Datetime (2005,03,10,12,00,00)

node......................

manager.....................

preview

when i click preview with out changing any values in the boxes then i am able to see the report actual data i am passing in the url.

so what i need is after entering url i have to get the report based on the parametrs i am entering in the url. Instead of pressing refresh again in the report and then pressing preview. Can some one suggest me a solution for this. please i am in urgent need of it solution. help me out with this.

Thank you,
Reddy.Make sure you have unchecked "Save Data with Report" option