Architectural question

Hello all, I have a general architecture question.

We have a system that essentially does multiple processes/operations
against a single table.

The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.

In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.

To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)

So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.

Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?Hi,
I am not sure if you vertically split the tables the original problem
will go away. I think when these types of issues arise you might
consider partitioning the database (which is horizontal). That might
increase performance etc and solve locking problems too.

Amaxen1@.gmail.com wrote:

Quote:

Originally Posted by

Hello all, I have a general architecture question.
>
We have a system that essentially does multiple processes/operations
against a single table.
>
The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.
>
>
In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.
>
>
To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)
>
So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.
>
Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?

sql

Architectural Design Question ?

We currently have a number of SSAS 2000 physical cubes and a single KPI virtual cube that our finance department reports from.A colleague is prototyping a new approach in SSAS 2005 and I’m looking for some feedback.

Please see http://www.dailyware.com/SSAS2005Design.jpg for a high level overview.The lines in red illustrate custom .Net applications that push aggregated data from cubes to some other reporting source (SQL tables, XML?) that our finance department will report from.

Thought that come to my mind include: many points of failure, significant custom development required, none-mainstream, high maintenance cost….

Thanks,

Gary

Same as in AS2000 , in AS2005 you can consolidate several different cubes into a single one using new feature called Linked Measure groups.

Another point here is: you should look into providing your end users not only with static reports, but give them ability for Ad-hoc analysis of your data. There are quite a few applications talking directly to Analysis Services.
For instance, you will see great improvements in Pivot Tables in upcoming release of Office 2007.

As for the building redundancy into your system, you can use ether NLB clustering or MSCS ( microsoft clustering services ) solutions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I agree...my concern with the proposed design is the fact that it will make it difficult to use several tools coming out later this year. Moreover, our users have requirements to drill into data and this model makes that difficult.

Thanks for the input,

Gary

|||Let me ask another question....do you consider the attached design to be very non-mainstream and possibly difficult to support?|||

To save maitanance costs assosiated with having many cubes, many customers choose to create a single cube ( where possible) with multiple measure groups.

This is one of the main advantages switiching to AS2005, you can bring several fact tables into a single cube and point your reports and client appications to it.

You would start splitting cube into separate once if your calculation logic becomes complex and begins to slow down query performance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Gary,

Edward already provided detailed feedback - I would just add that, what jumped out at me was the KPI (SQL/XML) layer between the cubes and reporting applications. What is its purpose - you can easily create static reports directly from cubes, in addition to the ad hoc analytic capabilities that Edward mentioned?

What I can conceive of, in the context of KPI's, is the need for supporting metadata beyond what is provided for KPI's in AS 2005. Things like the role of a KPI within the framework of a customized Performance Management Scorecard - an example of this would be the Business Scorecard Manager:

http://office.microsoft.com/en-us/assistance/HA012225141033.aspx

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
HernnDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
atte,
Hernn|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
atte,
Hernn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribi en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernn
|
|

approach for sharing data between servers

hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
HernánDoing cross-server joins can be quite slow. Consider using replication and
replicate the two Person tables to SQL01 and SQL02. Then, do the joins
locally.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
hi
i have two "Person" tables
one in server SQL01
and the other in server SQL02
i will integrate these two tables
in a single "Person" table in other server SQL03
then, i think
all the "inner/left joins" to "Person" table
won't work because "Person" table was moved
how can i "join" to the "Person" table
that is in other server '
in other words, wich ways do i have
for accessing a table that is not
in the same server than the stored proc ?
what is the efficience hit ?
thanks
--
atte,
Hernán|||in that case
i will have a problem
with the (unique) ID of the table
how can i sincronize the ID ?
i think i will need a kind of semaphor
to lock a resource (table)
that holds the "NEXT-ID" ...
--
atte,
Hernán
"Tom Moreau" <tom@.dont.spam.me.cips.ca> escribió en el mensaje
news:O8vHpbfxGHA.4764@.TK2MSFTNGP02.phx.gbl...
| Doing cross-server joins can be quite slow. Consider using replication
and
| replicate the two Person tables to SQL01 and SQL02. Then, do the joins
| locally.
|
| --
| Tom
|
| ----
| Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
| SQL Server MVP
| Toronto, ON Canada
| .
| "bajopalabra" <bajopalabra@.hotmail.com> wrote in message
| news:O0IPCYfxGHA.1872@.TK2MSFTNGP05.phx.gbl...
| hi
| i have two "Person" tables
| one in server SQL01
| and the other in server SQL02
|
| i will integrate these two tables
| in a single "Person" table in other server SQL03
|
| then, i think
| all the "inner/left joins" to "Person" table
| won't work because "Person" table was moved
|
| how can i "join" to the "Person" table
| that is in other server '
|
| in other words, wich ways do i have
| for accessing a table that is not
| in the same server than the stored proc ?
| what is the efficience hit ?
|
| thanks
|
| --
| atte,
| Hernán
|
|

Appointment Finder Query...TSQL Expert Required!

We are designing a booking system and have a list of appointments in a single table with the start and end time of the appointment.

Is there any way to write a query to return the available time gaps, I'm thinking here that we would need to generate a temporary table in the query and return the data from this table that doesn't join with data in the appointment table but I'm guessing and was wondering whether there's an easier way..

The table definition is

CREATE TABLE [dbo].[SALON_Appointment] (
[AppointmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[StylistID] [int] NOT NULL ,
[Recurrency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstStartTime] [datetime] NOT NULL ,
[FirstEndTime] [datetime] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO

Thanks in advance

Hi

You don't say what version of SQL Server you are using and there are a number of pieces of information/constraints missing.

These question include:

What is the length of the working day - does midnight to 07:00 counts as free time? Does the system need to allow for breaks such as lunch time? You could book them as dummy appointments (probably need an appointment type field to allow them to be stripped out under certain circumstances). What is the minimum length for an appointment - is 09:30 to 09:35 a valid gap? What days do you need this for: All Days; Working Days; Days for which appointments exist.|||

I assume that you wana find the gap based on stylistID.

Code Snippet

SELECT A1.StylistID,

DATEDIFF(mi,A1.EndTime, A2.StartTime) AS Gap

FROM Appointment A1 JOIN Appointment A2 ON (A1.StylistID=A2.StylistID)

WHERE (A1.EndTime=

(SELECT MAX(A3.EndTime)

FROM Appointment A3

WHERE (A3.StylistID = A1.StylistID)

AND (A3.EndTime <= A2.StartTime)))

Hope this helps,

Paraclete

|||Hi,

Thanks for the reply.
I think I've worked a solution out which is similar to your method. I create a temporary and fill it with psuedo appointments, I then do a select which uses a sub query with NOT EXISTS to select items from the pseudo table that don't exist in the real appointment table and hence returns a list of available slots.

It looks like a little less code than your method but I've not tested it fully so I'm not sure whether its 100% yet.

application development and permissions

Using SS2000 SP4. We have an application that will use a single user to logi
n
from the application after development. We will use only stored procedures
and views to access the data.
I was thinking of creating a "developer" login for the developer and
assigning it to the db_owner role. As I understand it, this would make the
owner of the tables, stored procedures and views "dbo". After development,
assuming there is no dynamic sql used, would I then be able to give execute
permission on the stored procedures and select permissions on the views to
the application user and have everything work?
Thanks,
Dan D.Dan
Sure
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
> Using SS2000 SP4. We have an application that will use a single user to
> login
> from the application after development. We will use only stored procedures
> and views to access the data.
> I was thinking of creating a "developer" login for the developer and
> assigning it to the db_owner role. As I understand it, this would make the
> owner of the tables, stored procedures and views "dbo". After development,
> assuming there is no dynamic sql used, would I then be able to give
> execute
> permission on the stored procedures and select permissions on the views to
> the application user and have everything work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Uri Dimant" wrote:

> Dan
> Sure
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
>
>

append single character at specific position in string

Hi everyone, i am in s of help.
I have a variable (called status) of type char(10). This needs to be
extended to type char(11) and i need to append the character 'N' at this
position. For example, if 'status' is any of the following strings of type
char(10)
NYNYYN NY
NNYYY
Y
NNNY YYYNN
These must be converted to the following strings of type char(11) ..<note
the 'N' at the end of the string>:
NYNYYN NYN
NNYYY N
Y N
NNNY YYYNNN
Would anyone know how this can be done?
Any help most appreciated.
Kindest regards,
Sarah.Based on you narrative, you seems you can simply concatenate. For example:
DECLARE @.status char(10)
SELECT @.status = 'NYNYYN NY'
SELECT @.status + 'N'
Hope this helps.
Dan Guzman
SQL Server MVP
"sarah.clarke" <s.clarke@.nospam.com> wrote in message
news:eSgTNXqHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>|||First off, alter the table and change the column to char(11). That's the
easy part.
update MyTable
set status = left(isnull(status,'') + ' ', 10) + 'N'
Note: There are 10 spaces between the quotes.
What this statement does is to convert any null values to an empty string.
Then, it concatenates 10 spaces.
Then, it takes the left most 10 characters.
Finally, it concatenates the 'N' as the eleventh character.
If you want null values to remain null, simply remove the isnull function.
All the concatenation will return null and the row will still have a null
value in the status column.
Hope that helps,
Joe
"sarah.clarke" wrote:

> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>
>|||oops...my previous post was regarding a column
using a variable:
declare @.status char(11)
set @.status = 'A'
set @.status = left(@.status, 10) + 'N'
Hope that helps,
Joe

Anyone using Excel 2007 as client ?

Overall pretty cool, but apparently you can't select a single level of a dimension in the pivot table.

For instance, I have level Year, Quarter, Month, Week, Day.

Having to look at last week's sales per day, I'd be selecting a specific week and I'd have the Day level only as row labels.

This worked in Excel 2003, but I can't figure out how to leave out the Year, Q, M and Week levels.

In the PivotTable Field List, you have a check box next to the Time dimension, but nothing next to each level in the dimension. So you have to have all levels of the dimension in the rows of the pivot.

This means a lot of useless drillthrough for the users as they go down from Year to Quarter to Month to Week to Days.

Am I missing something ?

In Excel 2007 you can display the Day level only by 'hiding' the other levels. If you drill down to the lowest level day, right click on any item | Show/Hide Fields | Uncheck the Year level - the year level is hidden. Repeat for the other levels you want to hide.

Hope this helps!

|||

Thanks Zaheera it does help, that's what I needed.

I think this is a feature that needs improvement, since I need 17 clicks before I have drilled down to Days, and then removed the levels one by one.

|||

To accomplish this in fewer clicks - you can right click on an item in the highest level | Expand/Collapse | Expand to <lowest level>. In one-click you've drilled down to the lowest level. Now you'll need to hide each level one by one as described above (which can be a pain - I agree). Or you can do this in one-step in VBA (Alt + F11 to open the editor, Cntrl + G to go to the immediate window): Activecell.PivotField.CubeField.HiddenLevels = 3 and the top 3 levels will be hidden at once.