performance question

Hi,
What's the best way to store a certain kind of information in an SQL-server database:
-having one table with many records (e.g. millions)
or
-having many tables (e.g. thousands) with less records (e.g. thousands)Your question cannot be answered with a simple "statement x is correct" as the answer varies wildly based on the scenario. The best way to find an answer is to do some reading. I suggest you start by reading up on normalisation (www.r937.com/relational.html).|||Ah, the ever popular answer: It depends!

With one pseudo-exception I'll explain in a moment, if you have one single type of thing, then one table is the best way to store it. If you have one thosand similar but slightly different things, then one thousand tables are usually better.

The one place that I'll make an exception to that rule (which is really just a clarification, not a true exception) is that lookups are a different kind of beast. While people sometimes argue that all lookups belong in a single table (this is known as One True Lookup Table or OTLT), lookups really belong in separate tables.

If you have 96 kinds of lookup items, those are really 96 quite different things. If you can put hat sizes and truck colors into a lookup table, but you can't exchange a hat size for a truck color (no one I know wears a "Sunburnt Orange" size hat or drives a 6 7/8 colored truck), then the hat sizes are a different thing than the truck colors. Because they are different things, they ought to be in a different table.

-PatP

Performance problems with sqlxml (FOR XML)

Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}
From .NET applications, you can use the managed SQLXML classes in SQLXML
3.0. See
http://www.microsoft.com/mspress/boo...#SampleChapter for
an overview.
From COM applications, you can do it with the SQLXML OLEDB provider in
SQLXML 3.0.
Here's a sample VB Script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adReadAll = -1
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML.DOMDocument")
cmd.Properties("xml root") = "QueryResults"
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
xmlDoc.Save "results.xml" 'or process the XML as appropriate
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}
|||Hello Graeme (and others),
thanx for the reply. Will the managed SQLXML classes also work with Java ?
If so, would you have an example of how to employ them to query SQL-Server
into XML ?
Thanx,
Joachim
"Graeme Malcolm" wrote:

> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/boo...#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> "SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
> Hello all,
> I am running into performance problems when handling larger data volumes and
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by line.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>
>
|||If you are using .Net C# then you should be able to get the result set as a
XmlReader from which you can directly create a DOM.
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:Ogr2tYQbFHA.1360@.TK2MSFTNGP10.phx.gbl...
> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/boo...#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> "SERVER=(local);DATABASE=northwind;INTEGRATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message
> news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
> Hello all,
> I am running into performance problems when handling larger data volumes
> and
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by
> line.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>

Performance problems with sqlxml (FOR XML)

Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}From .NET applications, you can use the managed SQLXML classes in SQLXML
3.0. See
http://www.microsoft.com/mspress/bo...p#SampleChapter for
an overview.
From COM applications, you can do it with the SQLXML OLEDB provider in
SQLXML 3.0.
Here's a sample VB Script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adReadAll = -1
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
" SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML.DOMDocument")
cmd.Properties("xml root") = "QueryResults"
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
xmlDoc.Save "results.xml" 'or process the XML as appropriate
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
Hello all,
I am running into performance problems when handling larger data volumes and
querying sql-server with the FOR XML-clause.
I have not found a way to directly retrieve a DOM-Object from my query.
Instead, until now, I write the resultset into a bufferString line by line.
This process is extremely slow (see code below).
--> Is there a better way to handle this ? Can I retrieve a DOM-Document
directly when using the FOR XML-clause ?
Thanx,
Joachim
************* CODE EXAMPLE ***********************
Statement stmt = conn.createStatement();
stmt.execute(query + " FOR XML AUTO, ELEMENTS");
ResultSet rs = stmt.getResultSet();
StringBuffer buffer = new StringBuffer();
while(rs.next())
{
InputStream is = rs.getAsciiStream(1);
InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
Reader in = new BufferedReader(isr);
int ch;
while ((ch = in.read()) > -1) {
buffer.append((char)ch);
}
in.close();
}|||Hello Graeme (and others),
thanx for the reply. Will the managed SQLXML classes also work with Java ?
If so, would you have an example of how to employ them to query SQL-Server
into XML ?
Thanx,
Joachim
"Graeme Malcolm" wrote:

> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/bo...p#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> " SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com..
.
> Hello all,
> I am running into performance problems when handling larger data volumes a
nd
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by line
.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>
>|||If you are using .Net C# then you should be able to get the result set as a
XmlReader from which you can directly create a DOM.
Chandra
---
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:Ogr2tYQbFHA.1360@.TK2MSFTNGP10.phx.gbl...
> From .NET applications, you can use the managed SQLXML classes in SQLXML
> 3.0. See
> http://www.microsoft.com/mspress/bo...p#SampleChapter for
> an overview.
> From COM applications, you can do it with the SQLXML OLEDB provider in
> SQLXML 3.0.
> Here's a sample VB Script:
> Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
> Const adExecuteStream = 1024
> Const adReadAll = -1
> Const adCmdStoredProc = 4
> Dim conn
> Set conn = CreateObject("ADODB.Connection")
> conn.Provider = "SQLXMLOLEDB.3.0"
> conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
> " SERVER=(local);DATABASE=northwind;INTEGR
ATED SECURITY=sspi;"
> conn.Open
> Dim cmd
> Set cmd = CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = conn
> 'Set the dialect
> cmd.Dialect = DBGUID_SQL
> Dim xmlDoc
> Set xmlDoc= CreateObject("MSXML.DOMDocument")
> cmd.Properties("xml root") = "QueryResults"
> cmd.Properties("Output Stream") = xmlDoc
> 'Specify the command
> cmd.CommandText = "SELECT * FROM Products FOR XML AUTO"
> cmd.CommandType = adCmdStoredProc
> 'Execute the command returning a stream
> cmd.Execute, , adExecuteStream
> xmlDoc.Save "results.xml" 'or process the XML as appropriate
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Joachim Blankenstein" <Joachim Blankenstein@.discussions.microsoft.com>
> wrote in message
> news:75F8366C-32BB-4FB5-83EF-4B3209640E12@.microsoft.com...
> Hello all,
> I am running into performance problems when handling larger data volumes
> and
> querying sql-server with the FOR XML-clause.
> I have not found a way to directly retrieve a DOM-Object from my query.
> Instead, until now, I write the resultset into a bufferString line by
> line.
> This process is extremely slow (see code below).
> --> Is there a better way to handle this ? Can I retrieve a DOM-Document
> directly when using the FOR XML-clause ?
> Thanx,
> Joachim
> ************* CODE EXAMPLE ***********************
> Statement stmt = conn.createStatement();
> stmt.execute(query + " FOR XML AUTO, ELEMENTS");
> ResultSet rs = stmt.getResultSet();
> StringBuffer buffer = new StringBuffer();
> while(rs.next())
> {
> InputStream is = rs.getAsciiStream(1);
> InputStreamReader isr = new InputStreamReader(is,"ISO-88591");
> Reader in = new BufferedReader(isr);
> int ch;
> while ((ch = in.read()) > -1) {
> buffer.append((char)ch);
> }
> in.close();
> }
>