Database

Best practice in large tables

1-Apr-07 3:34

Both the table (Marks and Ctrl) have a common field "ctrlkey" and have relation between them.
Please re-think upon it and do needful as early possible.

Re: Select Query

Krish - KP6-Apr-07 4:42

Krish - KP

6-Apr-07 4:42

this cannot be done using a simple SELECT query.

1. Create a cursor for "Marks" table
2. create a cursor for "Ctrl" table which fetches corresponding data for hte ctrlkey fetched by outer cursor
3. in the inner look you can arrange the data as you required

Regards
KP

Nader Elshehabi31-Mar-07 10:10

Nader Elshehabi

31-Mar-07 10:10

Hello all

This may seem a simple question, but I'm not looking for a solution, I'm looking for a best practice. I have a SQLTable that contains 293 columns, and may even contain thousands of rows. Such a massive table could be a source of performance issues.

My question is: Is it satisfactory to keep the table this way, or should it be better if I divide the table on more than one tables, and link them using Foreign Key constraints? For example each Table containing 50 columns or so?

Waiting eagerly for your expertise concerning this issue.

Regards

Colin Angus Mackay31-Mar-07 10:23

31-Mar-07 10:23

Nader Elshehabi wrote:
I have a SQLTable that contains 293 columns, and may even contain thousands of rows. Such a massive table could be a source of performance issues.

Absolutely - But, it really depends on how you want to use the table.

Nader Elshehabi wrote:
Is it satisfactory to keep the table this way, or should it be better if I divide the table on more than one tables, and link them using Foreign Key constraints?

To me that suggests that the table is not normalised. All data in the table must be dependent on the primary key. If you have repeating data in a table that suggests that a separate table needs to be created. For example:

A table Family: MotherName, FatherName, Child1Name, Child2Name, Child3Name, Child4Name

This has some limitations. First a maximum of 4 children are permitted. It assumes everyone lives in a perfect home and all siblings share the same parents.

So, if you have repeating columns (often characterised by the need to include a digit) then that needs to be separated out into a new table.

Nader Elshehabi wrote:
For example each Table containing 50 columns or so?

You should search for useful relationships rather than splitting at an arbitrary number of columns. By splitting at every 50th column you run the risk of splitting related data (or data that is often used together) into separate tables which will slow down any query.

If there is no way to normalise the data then consider what groups of columns tend to be used together. Then split it that way ensuring that each table maintains a one-to-one relationship with the others. This will cause some issues during an insert where some rows won't exist yet. So, design it to accept a zero-to-one relationship - that way missing rows in other tables don't affect the whole. You might also find that there a groups of columns where if one column isn't filled, neither are others in the group. You can then use this knowledge to eliminate the need to store a row where no information is used.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Nader Elshehabi31-Mar-07 10:36

Nader Elshehabi

31-Mar-07 10:36

Thank you Colin for your prompt reply I really appreciate it.

The table describes a medical procedure which contains 293 parameters to be filled in the report. Some of them of course can be null, but non of them is a repetition. Each procedure -ie. row- is given a GUID unique to that procedure used as the primary key.

My idea about dividing the table wasn't at the exact 50th column -maybe I wasn't clear on this point, sorry-. The procedure can be -somehow- roughly divided into several steps, each containing about 50 parameters. As you said this will give me a great headache in maintaining the relations between the tables using the procedure's GUID as a foreign key. Also in the quesries, this is going to be very troublesome.

Currently the table may work fine, but my concern is when it's filled with thousands of rows. Is it worth the effort to divide the table the way you suggested, or will it work fine as a single 293 columns containing table in the future?

Regards

kubben31-Mar-07 14:41

kubben

31-Mar-07 14:41

I don't think you need to worry about thousands of rows as much as what is the total number of bytes with your 293 columns. If you are using sql 2005 then your page size is 8060 bytes. You really don't want a single row to go over the page size, that can cause a lot of performace issues. If you can say that your total max row size would be 6000 bytes then I wouldn't worry about performace. If you max row size is over 8060 bytes then you really should break up the columns.
If you only have thousands like 10,000 rows in a table there is a pretty good chance that any query wouldn't even use an index. Which is the next point. Performace has more to do with indexes being set up properly then number of columns. Sql server always looks up full pages. So the smaller the row size the more rows that will be read with one page read. Anyway, that is probably a lot more then you wanted.

Here's a link to potential problems you might have:
http://www.sqlservercentral.com/columnists/awarren/2862.asp[^]

Hope that helps.
Ben

SQL Stored Procedure Column

Nader Elshehabi31-Mar-07 21:58

Nader Elshehabi

31-Mar-07 21:58

Thank you so much kubben. You reply has been very helpful and guiding.

Kindest Regards Rose | [Rose]

Lea Hayes31-Mar-07 9:24

Lea Hayes

31-Mar-07 9:24

Hi,

I am using MSSQL2005 and am having difficulties understanding how to create a stored procedure which first verfies data is found, and then if so processes that data.

ALTER PROCEDURE dbo.PostNews
@Subject VARCHAR(255),
@Body TEXT
AS
/* Has news already been posted within the last 10 minutes? */
IF EXISTS(SELECT TOP 1 DATEDIFF(MINUTE, tableNews.CreateDate, GETDATE()) AS MinuteDiff FROM tableNews WHERE tableNews.Subject=@Subject) BEGIN

/** THIS DOES NOT WORK **/
IF(MinuteDiff <= 10) RETURN 1;
END

/* Post news entry. */

RETURN 0;
RETURN

If possible it would be great to find out how you guys attempt these types of SQL statements. What could I do to resolve the above issue, if the result is found I want to return 1, otherwise return 0 once the news posting has been added.

Many thanks,
Lea Hayes

Re: SQL Stored Procedure Column

Colin Angus Mackay31-Mar-07 10:09

Re: SQL Stored Procedure Column

31-Mar-07 10:09

lhayes00 wrote:
/** THIS DOES NOT WORK **/
IF(MinuteDiff <= 10) RETURN 1;
END

Because MinuteDiff does not exist at this point in the code.

Okay - Let's look at the IF statement

lhayes00 wrote:
IF EXISTS(SELECT TOP 1 DATEDIFF(MINUTE, tableNews.CreateDate, GETDATE()) AS MinuteDiff FROM tableNews WHERE tableNews.Subject=@Subject)

IF EXISTS(SELECT) does a check to discover if the SELECT statement will return any rows. It doesn't care what those rows are, or store the results anywhere. It is a simple: Is anything at all returned.

Because of that, any calculations performed in the SELECT clause (in the place you'd normally return column data) is ignored. Putting a TOP 1 is pointless because the operation will short circuit the moment any row is found. Putting a DATEDIFF function in is pointless because it will ignore the results.

What you might want to do instead is something like this:

DECLARE @tenMinutesAgo DATETIME
SET @tenMinutesAgo = DATEADD(minute, -10, GETDATE())
IF EXISTS (SELECT * FROM tableNews WHERE CreateDate >= @tenMinutesAgo AND Subject=@Subject)
BEGIN
    RETURN 1;
END
RETURN 0;

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Lea Hayes31-Mar-07 10:17

Lea Hayes

31-Mar-07 10:17

Thankyou very much for your reply Big Grin | :-D

I have just tried running that in my project and it works perfectly!!
And in addition is somewhat more optimal which is great.

Re: SQL Stored Procedure Column

Colin Angus Mackay31-Mar-07 10:24

31-Mar-07 10:24

You're welcome. I'm glad it worked.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

self join

zizu9096@gmail.com31-Mar-07 5:31

zizu9096@gmail.com

31-Mar-07 5:31

When to use an inner join and when to use connect by prior to clause .

Re: self join

Colin Angus Mackay31-Mar-07 10:13

31-Mar-07 10:13

zizu9096@gmail.com wrote:
When to use an inner join and when to use connect by prior to clause .

I don't recognise a "connect by prior to" clause. I couldn't find it in the documentation. Could you please cite the source of this phrase?

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Re: self join

DQNOK2-Apr-07 6:55

How to calll Oracle stored procedures from Microsoft Reporting Services

2-Apr-07 6:55

If you are referring to the CONNECT BY clause available within Oracle, that and self-joins are two entirely different things. The CONNECT BY allows recursion in the case that a table contains parent-child relationships. Using the CONNECT BY clause enables one to select ALL the children/grand-children/etc given a root entry. You can also use it to locate the root given a (perhaps distant) node. Joins on the other hand are not recursive. As nearly as I've ever been able to use them, you're limited to two generations of parent-child (unless you bury layers of sub-queries or some such thing).

maskor ma31-Mar-07 1:36

maskor ma

31-Mar-07 1:36

I wanted to know how can we call Oracle stored procedures from Microsoft Reporting Services. I could pass a sql text and get a record set, but I want to execute a store proc and get the record set, and kindly let me know how to pass a refcursor to get the record set into the reporting services.

As always.. quick response would be greatly appreciated.

Thanks

Error: DataBinding: 'System.Data.DataRowView' [modified]

nothingbutcat30-Mar-07 16:25

nothingbutcat

30-Mar-07 16:25

anyone can help?

'/aspx' 應用程式中發生伺服器錯誤。
-----------------------------------------------------------------------------
DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。
描述: 在執行目前 Web 要求的過程中發生未處理的例外情形。請檢閱堆疊追蹤以取得錯誤的詳細資訊，以及在程式碼中產生的位置。

例外詳細資訊: System.Web.HttpException: DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。

原始程式錯誤:

只有在偵錯模式編譯時，才可以顯示產生此未處理例外狀況的原始程式碼。若要啟動，請依照下列步驟之一，然後要求 URL:

1. 將 "Debug=true" 指示詞加入產生錯誤的程式碼頂端。例如:

<%@ Page Language="C#" Debug="true" %>

或:

2. 將下列區段加入您應用程式的組態檔:

請注意: 第二種技巧會導致在偵錯模式下編譯指定應用程式中的所有檔案。第一種技巧只會造成在偵錯模式下編譯該特定檔案。

重要資訊: 在偵錯模式下執行應用程式會過度耗用記憶體/效能。在實際執行部署之前，應該先確認應用程式已經停止偵錯。

堆疊追蹤:

[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' 沒有包含名為 '書名' 的屬性。]
Microsoft.VisualBasic.CompilerServices.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags) +272
Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure) +196
Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn) +216
ASP.bookstore_default_aspx.Data_Bind(DataView DView, Object BindObject) +91
ASP.bookstore_default_aspx.Cart_Add(String Title, String Price) +823
ASP.bookstore_default_aspx.AddBookToCart(Object sender, DataGridCommandEventArgs e) +247
System.Web.UI.WebControls.DataGrid.OnItemCommand(DataGridCommandEventArgs e) +105
System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +77
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +117
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

--------------------------------------------------------------------------------
版本資訊: Microsoft .NET Framework 版本:2.0.50727.42; ASP.NET 版本:2.0.50727.210

-- modified at 22:50 Friday 30th March, 2007

Complex Select Query

Girish48130-Mar-07 7:48

30-Mar-07 7:48

There is a table name "ResultData" which following data :

Rollno Result SchoolCode
1 First 1
2 First 1
3 Second 2
4 First 1
5 Fail 2
6 Third 2
7 Second 1
8 Fail 3
9 Fail 1
10 Second 2

I want the output like this :

(School code wise count of Result)

SchoolCode First Second Third Fail Total
1 3 1 0 1 5
2 0 2 1 1 4
3 0 0 0 1 1

Please write a single select query to get output.
Thanks in advance

Girish Kumar Sharma

DQNOK30-Mar-07 8:12

30-Mar-07 8:12

I believe you want a "crosstab query". Apart from Microsoft products, I don't know if other vendors have it built-in. I don't believe it's part of the SQL standard...

Here is what Microsoft Access help says about cross tab queries.
"
Syntax
TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

The TRANSFORM statement has these parts:

Part Description
aggfunction An SQL aggregate function that operates on the selected data.
selectstatement A SELECT statement.
pivotfield The field or expression you want to use to create column headings in the query's result set.
value1, value2 Fixed values used to create column headings.
"

Girish48130-Mar-07 8:28

30-Mar-07 8:28

So, is it not possible to write a select statement in SQL Sever ?

DQNOK30-Mar-07 8:39

30-Mar-07 8:39

Well, I thought it was included in SQL Server... Looks like didn't show-up until 2005.

Here is one article that may help. http://builder.com.com/5100-6388_14-6143761.html[^]

Once upon a time I wrote a lot of Perl code to dynamically do the same thing that the crosstab query does, but when I discovered crosstabs, I never went back. I don't know how to do it using CASE statements.

sorry I can't help more. Maybe someone else can...

Jerry Hammond30-Mar-07 15:16

Jerry Hammond

30-Mar-07 15:16

You're correct, PIVOTs don't show up until 2005 in MSSQL

“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan

DQNOK30-Mar-07 12:49

30-Mar-07 12:49

Also check out

http://www.oreillynet.com/pub/a/network/2004/12/17/crosstab.html?CMP=ILC-FV7511446129&ATT=2411[^]

for a good explanation, and (possibly) a pre-written procedure you (might) be able to use.

Girish48130-Mar-07 21:23