Need help / suggestion

Hey guys

I have to implement a dynamic Parent - > Child Scenario ... but the catch is
as Follows :

I need to create this Table Design so that I can have multiple Parent ->
child - > parent Relationships

(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folder
that holds another Folder etc to Infinite )

So if the Above makes any sense ... Suggestions would be Welcome

Thanx1) Get a copy of TREES & HIERARCHIES IN SQL for several different
methods

2) Google for "nested sets", "path enumeration" and "adjacency list"
models in SQL|||Thanx !!!

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL|||Nice 1 celko !!! :P
http://www.intelligententerprise.co...equestid=315563

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL

Need Help - Custome Paging Using ROW_NUMBER()

Hi,

I am attempting to implement a custome paging solution for my web Application, I have a table that has 30,000 records and I need to bw able to page through these using a Gridview. Here is my curent code but it generates an error when I try to compile the Stored Procedure, I get the following errors:

<Error messages>

These are on the first SELECT Line..

Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "dbo.FUNERAL.NUMBER" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "mort.NAME.CODE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.LAST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.FIRST_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAME.MIDDLE_NAME" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "NAMETYPE.TYPE" could not be bound.
Msg 4104, Level 16, State 1, Procedure proc_NAMEGetPaged, Line 17
The multi-part identifier "FUNERAL.NUMBER" could not be bound.

</Error Messages>

<Sotred Procedure>

CREATE PROCEDURE proc_NAMEGetPaged
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT NAME.CODE, NAME.LAST_NAME, NAME.FIRST_NAME + ' ' + NAME.MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER
FROM
(SELECT CODE, LAST_NAME, FIRST_NAME + ' ' + MIDDLE_NAME AS Name, NAMETYPE.TYPE, FUNERAL.NUMBER,
ROW_NUMBER() OVER(ORDER BY LAST_NAME) as RowNum
FROM Name n) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

</Stored Procedure>

Any assistance in resolving this would be greatly appreciated..

Regards..

Peter.

Hi,

Have resolved my problem see code below..

<Stored Procedure>

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Peter Annandale
-- Create date: 21/10/2006
-- Description: Return Subset from Names table
-- =============================================
CREATE PROCEDURE proc_NAMEGetPaged
-- Add the parameters for the stored procedure here
@.startRowIndex int,
@.maximumRows int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT CODE, LAST_NAME, Name, TYPE, NUMBER
FROM
(SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER,
ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum
FROM dbo.NAME n
LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE
LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED) as NameInfo
WHERE RowNum BETWEEN @.startRowIndex AND (@.startRowIndex + @.maximumRows) -1
END
GO

< /Stored Procedure>

Regards..

Peter.

Need Extra Rows

Here is the basic sql I am trying to implement:
select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123'
group by classid, dtmready
order by dtmready;
Here is my result set:
A1 3 2006-07-01 00:00:00.000 LUP
A1 10 2006-08-15 00:00:00.000 LUP
A1 11 2006-09-15 00:00:00.000 LUP
A1 10 2006-10-15 00:00:00.000 LUP
A1 10 2006-11-01 00:00:00.000 LUP
A1 10 2006-11-30 00:00:00.000 LUP
A1$ 2 2006-11-01 00:00:00.000 LUP
A1$ 2 2006-11-30 00:00:00.000 LUP
A2$ 3 2006-07-01 00:00:00.000 LUP
A3$ 3 2006-08-15 00:00:00.000 LUP
A3$ 2 2006-09-15 00:00:00.000 LUP
A3$ 2 2006-10-15 00:00:00.000 LUP
B1 1 2006-04-14 16:50:46.910 OTHER
B1 5 2006-07-01 00:00:00.000 LUP
B1 26 2006-08-15 00:00:00.000 LUP
B1 24 2006-09-15 00:00:00.000 LUP
B1 25 2006-10-15 00:00:00.000 LUP
B1 10 2006-11-01 00:00:00.000 LUP
B1 8 2006-11-30 00:00:00.000 LUP
B1$ 3 2006-09-15 00:00:00.000 LUP
B1$ 4 2006-10-15 00:00:00.000 LUP
B1$ 2 2006-11-01 00:00:00.000 LUP
B1$ 4 2006-11-30 00:00:00.000 LUP
B2$ 5 2006-08-15 00:00:00.000 LUP
B2$ 3 2006-09-15 00:00:00.000 LUP
B2$ 1 2006-10-15 00:00:00.000 LUP
B3$ 1 2006-09-15 00:00:00.000 LUP
B3$ 2 2006-10-15 00:00:00.000 LUP
T1 3 2006-05-19 00:00:00.000 LUP
T1 7 2006-06-30 00:00:00.000 LUP
T1$ 2 2006-06-30 00:00:00.000 LUP
If you notice for the most classids, the earliest dtmready is > today. What I
need is to return an additional row when the earliest dtmready is after today.
The desired rows would be:
A1 0 (today's date)
etc
Background: I am running SQL Server 2000 SP4 and the results of the query are
returned to a java program at a level where I do not have the ability to
create a new row. So, it would be ideal if I could create the sql that
returns a row with a dtmready of today with a count of 0 for each classid
that has a minimum dtmready > today.Hi
The easiest way to do this is to use a calendar table see
http://www.aspfaq.com/show.asp?id=2519 then your query would be
SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
CROSS JOIN dbo.calendar c
LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
u.rmpropid = '123'
GROUP BY v.classid, c.dt
ORDER BY c.dt
The cross join will get all date and classid combinations.
John
"michaelloveusa" <u20878@.uwe> wrote in message news:5ec904b492d8c@.uwe...
> Here is the basic sql I am trying to implement:
> select classid, count(*) as [COUNT], dtmready from unit
> where rmpropid = '123'
> group by classid, dtmready
> order by dtmready;
> Here is my result set:
> A1 3 2006-07-01 00:00:00.000 LUP
> A1 10 2006-08-15 00:00:00.000 LUP
> A1 11 2006-09-15 00:00:00.000 LUP
> A1 10 2006-10-15 00:00:00.000 LUP
> A1 10 2006-11-01 00:00:00.000 LUP
> A1 10 2006-11-30 00:00:00.000 LUP
> A1$ 2 2006-11-01 00:00:00.000 LUP
> A1$ 2 2006-11-30 00:00:00.000 LUP
> A2$ 3 2006-07-01 00:00:00.000 LUP
> A3$ 3 2006-08-15 00:00:00.000 LUP
> A3$ 2 2006-09-15 00:00:00.000 LUP
> A3$ 2 2006-10-15 00:00:00.000 LUP
> B1 1 2006-04-14 16:50:46.910 OTHER
> B1 5 2006-07-01 00:00:00.000 LUP
> B1 26 2006-08-15 00:00:00.000 LUP
> B1 24 2006-09-15 00:00:00.000 LUP
> B1 25 2006-10-15 00:00:00.000 LUP
> B1 10 2006-11-01 00:00:00.000 LUP
> B1 8 2006-11-30 00:00:00.000 LUP
> B1$ 3 2006-09-15 00:00:00.000 LUP
> B1$ 4 2006-10-15 00:00:00.000 LUP
> B1$ 2 2006-11-01 00:00:00.000 LUP
> B1$ 4 2006-11-30 00:00:00.000 LUP
> B2$ 5 2006-08-15 00:00:00.000 LUP
> B2$ 3 2006-09-15 00:00:00.000 LUP
> B2$ 1 2006-10-15 00:00:00.000 LUP
> B3$ 1 2006-09-15 00:00:00.000 LUP
> B3$ 2 2006-10-15 00:00:00.000 LUP
> T1 3 2006-05-19 00:00:00.000 LUP
> T1 7 2006-06-30 00:00:00.000 LUP
> T1$ 2 2006-06-30 00:00:00.000 LUP
> If you notice for the most classids, the earliest dtmready is > today.
> What I
> need is to return an additional row when the earliest dtmready is after
> today.
> The desired rows would be:
> A1 0 (today's date)
> etc
> Background: I am running SQL Server 2000 SP4 and the results of the query
> are
> returned to a java program at a level where I do not have the ability to
> create a new row. So, it would be ideal if I could create the sql that
> returns a row with a dtmready of today with a count of 0 for each classid
> that has a minimum dtmready > today.|||Hi John. Thanks for the response. I do not think I will be allowed to create
a calendar table (at least in ant reasonable amount of time), so do you have
any other ideas on how this might be accomplished?
Mike
John Bell wrote:
>Hi
>The easiest way to do this is to use a calendar table see
>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>CROSS JOIN dbo.calendar c
>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>u.rmpropid = '123'
>GROUP BY v.classid, c.dt
>ORDER BY c.dt
>The cross join will get all date and classid combinations.
>John
>> Here is the basic sql I am trying to implement:
>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Hi
You could do it as a temporary or derived table, but this would be extra
processing.
John
"michaelloveusa via SQLMonster.com" <u20878@.uwe> wrote in message
news:5eeb370967a4c@.uwe...
> Hi John. Thanks for the response. I do not think I will be allowed to
> create
> a calendar table (at least in ant reasonable amount of time), so do you
> have
> any other ideas on how this might be accomplished?
> Mike
> John Bell wrote:
>>Hi
>>The easiest way to do this is to use a calendar table see
>>http://www.aspfaq.com/show.asp?id=2519 then your query would be
>>SELECT v.classid, COUNT(u.classid) AS [COUNT], c.dt AS [dtmready]
>>FROM ( SELECT DISTINCT classid FROM unit WHERE rmpropid = '123' ) v
>>CROSS JOIN dbo.calendar c
>>LEFT JOIN Unit u ON c.dt = u.dtmready AND u.classid = v.classid AND
>>u.rmpropid = '123'
>>GROUP BY v.classid, c.dt
>>ORDER BY c.dt
>>The cross join will get all date and classid combinations.
>>John
>> Here is the basic sql I am trying to implement:
>>[quoted text clipped - 52 lines]
>> returns a row with a dtmready of today with a count of 0 for each
>> classid
>> that has a minimum dtmready > today.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Thanks for your help John. I figured out a way to do it with a union.
Mike
John Bell wrote:
>Hi
>You could do it as a temporary or derived table, but this would be extra
>processing.
>John
>> Hi John. Thanks for the response. I do not think I will be allowed to
>> create
>[quoted text clipped - 27 lines]
>> classid
>> that has a minimum dtmready > today.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1

Need Direction on WHAT to Implement...

Please understand that I am not asking HOW to do something - but, rather, I
just need some advise on what "technology" or method I should employ...
The problem is this:
I have a client for whom I am developing a web site. The client is a bank -
therefore, the entire site will be secure (SSL).
The banks' customers will be entering account number information to the
site - and we will be storing all inputs into a SQL Server database. The SQL
Server database resides on one of OUR servers.
The bank client then wants to periodically download, on demand, the
information that its customers have entered. (And the bank wants to download
the information entered in Excel spreadsheet format.)
I need to determine how I am going to get the entered information from our
ASP.NET server to our SQL Server database in a format that will be
unreadable to us (me, my company).
Likewise, I need to make the information available to the bank to download
in a format that they CAN read.
Where do I start'
I am an experienced, MCSD.NET certified developer - and I can implement
anything.
I just need to know where to begin.
Many thanks for your assistance!
~ Celia ~?
I am an experienced, MCSD.NET certified developer - and I can implement
anything.
"Celia Oblinger" <Oblinger@.comporium.net> wrote in message
news:b208a994.0401151834.259491a1@.posting.google.com...

quote:

---

> Please understand that I am not asking HOW to do something - but, rather,

---

I

quote:

---

> just need some advise on what "technology" or method I should employ...
> The problem is this:
> I have a client for whom I am developing a web site. The client is a

---

bank -

quote:

---

> therefore, the entire site will be secure (SSL).
> The banks' customers will be entering account number information to the
> site - and we will be storing all inputs into a SQL Server database. The

---

SQL

quote:

---

> Server database resides on one of OUR servers.
> The bank client then wants to periodically download, on demand, the
> information that its customers have entered. (And the bank wants to

---

download

quote:

---

> the information entered in Excel spreadsheet format.)
> I need to determine how I am going to get the entered information from our
> ASP.NET server to our SQL Server database in a format that will be
> unreadable to us (me, my company).
> Likewise, I need to make the information available to the bank to download
> in a format that they CAN read.
> Where do I start'
> I am an experienced, MCSD.NET certified developer - and I can implement
> anything.
> I just need to know where to begin.
> Many thanks for your assistance!
> ~ Celia ~

---

|||You could create a SQL DTS package that would extract the data from SQL and
put it into a new Excel Spreadsheet on demand.
Take a look at some examples out on http://sqldts.com
Then just use SSL between IIS and SQL to protect the data. The data will
not however be encrypted in SQL. You'll need to use third party tools to
do this.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql

Need Direction in WHAT to Implement...

Please understand that I am not asking HOW to do something - but, rather, I
just need some advise on what "technology" or method I should employ...
The problem is this:
I have a client for whom I am developing a web site. The client is a bank -
therefore, the entire site will be secure (SSL).
The banks' customers will be entering account number information to the
site - and we will be storing all inputs into a SQL Server database. The SQL
Server database resides on one of OUR servers.
The bank client then wants to periodically download, on demand, the
information that its customers have entered. (And the bank wants to download
the information entered in Excel spreadsheet format.)
I need to determine how I am going to get the entered information from our
ASP.NET server to our SQL Server database in a format that will be
unreadable to us (me, my company).
Likewise, I need to make the information available to the bank to download
in a format that they CAN read.
Where do I start'
I am an experienced, MCSD.NET certified developer - and I can implement
anything.
I just need to know where to begin.
Many thanks for your assistance!
~ Celia ~Celia,
One thought I have on this issue is to ensure the data stored in the SQL
Database is encrypted using a strong encryption package. There are several
good third party applications that can accomplish this objective for you.
The trick then is to control where and how the data can be displayed in an
unencrypted format. If the data is encrypted in the data files, the data
is not compromised even if someone obtains a copy of the mdf and ldf files.
Control of the decryption key is crucial.
Hopefully this information will get you started on the project.
Thanks.
Gary Whitley
This posting is provided "AS IS" with no warranties, and confers no rights.

Need Access-like security of SQL Server Express

Is there a way to implement Access-like password protection on a SQL Server Express dataset?

The database will be deployed on individual's PCs with no centralization of control. I want to restrict users from being able to see table definitions, stored procedures, etc. Access-like password protection is what I want, but I don't see any similar feature within SQL Server Express. Am I missing something?

hi,

at current time SQL Server does not provide this kind of "protection".. you can just encrypt stored procedures/views/user defined functions, you can even encrypt data, but you can not "hide" the database metaschema of the included tables..

regards

Need a fast queue using a table

I am trying to implement a very fast queue using SQL Server.

The queue table will contain tens of millions of records.

The problem I have is the more records completed, the the slower it
gets. I don't want to remove data from the queue because I use the
same table to store results. The queue handles concurrent requests.

The status field will contain the following values:
0 = Waiting
1 = Started
2 = Finished

Any help would be greatly appreciated.

Here is a simplified script to demonstrate what has been done.

CREATE TABLE [dbo].[Queue] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[JobID] [int] NOT NULL ,
[Status] [tinyint] NOT NULL
) ON [PRIMARY]
GO

CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.NextItem
@.JobID integer,
@.ID integer output
AS
SELECT TOP 1 @.ID = [ID]
FROM Queue WITH (READPAST, XLOCK)
WHERE (Status = 0) AND (JobID = @.JobID)
RETURN
GOmy idea: i would use 3 different tables, one for waiting entries,
one for the running entries and one for the finished.
And when status is changing instead of updating field "status"
(which is now not longer necessary) move the record from one
table to the other.

hth,
Helmut

"Chris Foster" <chrisfoster@.btinternet.com> schrieb im Newsbeitrag
news:b311a0b8.0307020655.9a5beeb@.posting.google.co m...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||[posted and mailed, please reply in public]

Chris Foster (chrisfoster@.btinternet.com) writes:
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
>...
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO

Since you have no index on JobID, I am not surprise if this is running
slow beyond all belief. The index you have on Status is likely to be
worthless, not being selective enough.

Helmut Wss suggested using three tables, and this may be worth considering.

However, you should start with getting a decent index structure. It
is from the table definition unclear to be whether there can be more
than one row for the same JobID. If there is, add a clustered index on
JobID and Status. If JobID is infact unique, get rid of that Identity
column. If your aim is to get high speed, then you should trim the
table, since the smaller the rows, the more rows you can fit on a page,
and the faster you can do I/O.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for your reply

The script I submitted is a smaller sample of what I have. I forgot to
state that the JobID has a foreign key to another table, there will be
many JobIds. Am I correct in assuming that a foreign key is indexed?
I can't set the index to be clustered, because it will only allow one
clustered index, ID is the primary key and is clustered.

The final queue will contain more fields than stated, if this effects
the speed I guess I will have to use a separate queuing table.

My first thought was that if status is indexed and has a value between
0 and 2, the index would be sorted ascending and 0's would be at the
top, therefore would remain the same speed throughout the queue. I am
not sure exactly what indexing does behind the scenes, I should
probably look into this. What difference would it make creating a
composite index of JobID and Status?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AD579174Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in public]
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The queue table will contain tens of millions of records.
> > The problem I have is the more records completed, the the slower it
> > gets. I don't want to remove data from the queue because I use the
> > same table to store results. The queue handles concurrent requests.
> > The status field will contain the following values:
> > 0 = Waiting
> > 1 = Started
> > 2 = Finished
> >...
> > CREATE TABLE [dbo].[Queue] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [JobID] [int] NOT NULL ,
> > [Status] [tinyint] NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> > GO
> > CREATE PROCEDURE dbo.NextItem
> > @.JobID integer,
> > @.ID integer output
> > AS
> > SELECT TOP 1 @.ID = [ID]
> > FROM Queue WITH (READPAST, XLOCK)
> > WHERE (Status = 0) AND (JobID = @.JobID)
> > RETURN
> > GO
> Since you have no index on JobID, I am not surprise if this is running
> slow beyond all belief. The index you have on Status is likely to be
> worthless, not being selective enough.
> Helmut Wss suggested using three tables, and this may be worth considering.
> However, you should start with getting a decent index structure. It
> is from the table definition unclear to be whether there can be more
> than one row for the same JobID. If there is, add a clustered index on
> JobID and Status. If JobID is infact unique, get rid of that Identity
> column. If your aim is to get high speed, then you should trim the
> table, since the smaller the rows, the more rows you can fit on a page,
> and the faster you can do I/O.|||Hi Chris

If you are after speed and with those sorts of volumes, I would not be
recommending that you used SQL Server to implement a TP queue in the
first place to store requests that are either waiting or started.
There are other options worth considering including IBM WebSphere
MQSeries.

chrisfoster@.btinternet.com (Chris Foster) wrote in message news:<b311a0b8.0307020655.9a5beeb@.posting.google.com>...
> I am trying to implement a very fast queue using SQL Server.
> The queue table will contain tens of millions of records.
> The problem I have is the more records completed, the the slower it
> gets. I don't want to remove data from the queue because I use the
> same table to store results. The queue handles concurrent requests.
> The status field will contain the following values:
> 0 = Waiting
> 1 = Started
> 2 = Finished
> Any help would be greatly appreciated.
> Here is a simplified script to demonstrate what has been done.
> CREATE TABLE [dbo].[Queue] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [JobID] [int] NOT NULL ,
> [Status] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]
> GO
> CREATE PROCEDURE dbo.NextItem
> @.JobID integer,
> @.ID integer output
> AS
> SELECT TOP 1 @.ID = [ID]
> FROM Queue WITH (READPAST, XLOCK)
> WHERE (Status = 0) AND (JobID = @.JobID)
> RETURN
> GO|||Chris Foster (chrisfoster@.btinternet.com) writes:
> The script I submitted is a smaller sample of what I have. I forgot to
> state that the JobID has a foreign key to another table, there will be
> many JobIds. Am I correct in assuming that a foreign key is indexed?

No. There are no automatic indexes created on foriegn-key columns. You need
to add any index yourself.

> I can't set the index to be clustered, because it will only allow one
> clustered index, ID is the primary key and is clustered.

On second thought, a non-clustered index on (JobId, Status) is likely
to be ideal. You could add ID explicitly to this index, but it is in fact
already there, because for a non-clustered index, SQL Server uses the
clustered key as the address to the data page.

The point here is that you get a *covering index*, which means that
SQL Server can resolve the query from the index alone. This is good
for speed. Since the index nodes are smaller than the complete rows,
you get more rows per page, and fewer pages to read.

> My first thought was that if status is indexed and has a value between
> 0 and 2, the index would be sorted ascending and 0's would be at the
> top, therefore would remain the same speed throughout the queue. I am
> not sure exactly what indexing does behind the scenes, I should
> probably look into this.

Yes, you should. :-) In a non-clustered index on Status only, SQL
Server needs to go to the data pages to find JobId so it can compare
this condition. This can be a more expensive operation that scanning
the table from left to right, because some pages may have to be read
more than twice. SQL Server uses the statistics is has on Status
to determine whether using the index is a viable way to go. You can
force SQL Server to use the index, by means of an index hint. You may find
that this gives even worse performance.

> What difference would it make creating a composite index of JobID and
> Status?

Because now SQL Server has all the information to resolve the query in
the index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Indexing both JobID and Status has made the queue work at an acceptable speed.

Thanks you

Chris

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AF515713E6Yazorman@.127.0.0.1>...
> Chris Foster (chrisfoster@.btinternet.com) writes:
> > The script I submitted is a smaller sample of what I have. I forgot to
> > state that the JobID has a foreign key to another table, there will be
> > many JobIds. Am I correct in assuming that a foreign key is indexed?
> No. There are no automatic indexes created on foriegn-key columns. You need
> to add any index yourself.
> > I can't set the index to be clustered, because it will only allow one
> > clustered index, ID is the primary key and is clustered.
> On second thought, a non-clustered index on (JobId, Status) is likely
> to be ideal. You could add ID explicitly to this index, but it is in fact
> already there, because for a non-clustered index, SQL Server uses the
> clustered key as the address to the data page.
> The point here is that you get a *covering index*, which means that
> SQL Server can resolve the query from the index alone. This is good
> for speed. Since the index nodes are smaller than the complete rows,
> you get more rows per page, and fewer pages to read.
> > My first thought was that if status is indexed and has a value between
> > 0 and 2, the index would be sorted ascending and 0's would be at the
> > top, therefore would remain the same speed throughout the queue. I am
> > not sure exactly what indexing does behind the scenes, I should
> > probably look into this.
> Yes, you should. :-) In a non-clustered index on Status only, SQL
> Server needs to go to the data pages to find JobId so it can compare
> this condition. This can be a more expensive operation that scanning
> the table from left to right, because some pages may have to be read
> more than twice. SQL Server uses the statistics is has on Status
> to determine whether using the index is a viable way to go. You can
> force SQL Server to use the index, by means of an index hint. You may find
> that this gives even worse performance.
> > What difference would it make creating a composite index of JobID and
> > Status?
> Because now SQL Server has all the information to resolve the query in
> the index.

Near Real time updating of fact table

-what is the best method that to implement the near real time updating of a
fact table without affecting all those users who are reading from it?
-fact table in question is only 1.8gb in size, 3.1 million rows, expect near
real time updating of approx 1000 new fact table records every 15 min,
occassionaly updates of historical (modified) fact table records - this is
where i assume write locks v read lock conflict may occur
- i am only concerned for the crystal report users who are using the fact
table via sql views ie relational db, the cube at present can stay as write
an night and read during the day
- any help greatly appreciated
U can use DTS for this, keep in mind that DTS is version sensitive.
Message posted via http://www.sqlmonster.com
|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment and very
curious to find out how.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>
|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>

Near Real time updating of fact table

-what is the best method that to implement the near real time updating of a
fact table without affecting all those users who are reading from it?
-fact table in question is only 1.8gb in size, 3.1 million rows, expect near
real time updating of approx 1000 new fact table records every 15 min,
occassionaly updates of historical (modified) fact table records - this is
where i assume write locks v read lock conflict may occur
- i am only concerned for the crystal report users who are using the fact
table via sql views ie relational db, the cube at present can stay as write
an night and read during the day
- any help greatly appreciatedU can use DTS for this, keep in mind that DTS is version sensitive.
Message posted via http://www.droptable.com|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment and very
curious to find out how.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>|||Can you please elaborate on DTS being version sensitive?
I am also trying to achieve this in my data warehouse environment.
Thanks,
Jay
"Rakesh Doebe via droptable.com" wrote:

> U can use DTS for this, keep in mind that DTS is version sensitive.
> --
> Message posted via http://www.droptable.com
>