Page Numbers in Excel: table

Showing posts with label table. Show all posts

Wednesday, March 28, 2012

Parameter name Questions in SqlDataSource??

I have a table with with some column name includes a space. for example [Product ID] [Product Name] Instead of Product_ID, Product_Name. when I try to create a gridview and enable delete, insert. It just won't work.

I've been trying for several hours without success. When I click on delete. the page postback without any error, but the record doesn't get deleted or updated.

<asp:SqlDataSource id="sourceProducts" runat="server"
SelectCommand="SELECT [Product ID], [Product Name] FROM Products" ConnectionString="<%$ ConnectionStrings:mydb %>"
DeleteCommand="Delete from Products where [Product ID]=@.ProductID
UpdateCommand="UPDATE Products SET [Product Name]=@.ProductName WHERE [Product ID]=@.ProductID"
<UpdateParameters>
<asp:Parameter Name="ProductName" />
<asp:Parameter Name="ProductID" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32"/>
</DeleteParameters>
</asp:SqlDataSource
<asp:GridView ID="GridView2" runat="server" DataSourceID="sourceProducts"
AutoGenerateColumns="False" DataKeyNames="Product ID" >
<Columns>
<asp:BoundField DataField="Product ID" HeaderText="ID" ReadOnly="True" />
<asp:BoundField DataField="Product Name" HeaderText="Product Name"/>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True">
</asp:GridView>

Another testing I did was to use another table with no space in the Column name, Product_ID, Product_Name. and I can't name my parameter as PID, PNAME. I have to name it as @.Product_ID, @.Product_Name in order for Delete, update to work. My understanding is if I declare the parameter explicitly(<asp:Parameter Name="PID" />, I can use any name I want. Did I must missed something?

I'm new to ASP.NET, could someone help me?

Thanks.

First off, you're setting yourself up for trouble by using field names with spaces. In this case, the problem is with the discrepancy between your Parameter name "ProductID" and your DataKeyNames setting of "Product ID". When you are attempting to update or delete a record, your primary key Parameter is not being set as it's name differs from that of your DataKeyNames setting. The two need to be the same in order for this to work, but you also can't use a Parameter with a space in it at least in good practice. Therefore, switch back to using unspaced field names and make sure the primary key Parameter matches that of the DataKeyNames setting.

|||

Thanks for explaining the cause of the problem, it really helps. The above code is a simple demo, and I don't have control over the existing Database.

Is there a way to work arround it?

ddpp

|||

Your workaround will be to set these Parameters in code. By using the SqlDataSource.Updating and Deleting events, you can manually extract the DataKey from the GridView and set the command Parameter to its value. With this method, the parameters can have different names.

|||

Ed, thanks for the tip. It guides me to the solution

<script runat="server"> void SqlDataSource1_Updating(Object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@.id"].Value = e.Command.Parameters["@.ContactID"].Value; e.Command.Parameters["@.name"].Value = e.Command.Parameters["@.ContactName"].Value; e.Command.Parameters.Remove(e.Command.Parameters["@.ContactID"]); e.Command.Parameters.Remove(e.Command.Parameters["@.ContactName"]); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Parameter Name Mapping</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView AutoGenerateColumns="False" DataKeyNames="ContactID" DataSourceID="SqlDataSource1" ID="GridView1" runat="server"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ContactID" HeaderText="ContactID" InsertVisible="False" ReadOnly="True" SortExpression="ContactID" /> <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" /> </Columns> </asp:GridView> <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:Contacts%>" ID="SqlDataSource1" runat="server" SelectCommand="SELECT [ContactID], [ContactName] FROM [Contacts]" UpdateCommand="UpdateContactName" UpdateCommandType="StoredProcedure" OnUpdating="SqlDataSource1_Updating"> <UpdateParameters> <asp:Parameter Name="id" Type="Int32" /> <asp:Parameter Name="name" Type="String" /> </UpdateParameters> </asp:SqlDataSource> </div> </form></body></html>

sql

Parameter multi-value problem when using a stored procedure

Hi folks,
I am describing the issue as below:
1. create a stored procedure as below
....where age in (@.p_age)
note: age is the table coumn of table table1 with datatype tinyint
2. .... and create a second dataset for parameter @.p_age
select distinct age from table1
3. associate the parameter...run it
4. There is no problem with single value. But when two ages are selected,
I got error message, "...Erro convert data type nvarchar to tinyint"
Please advise. PeterYou cannot pass and use multi-value parameters to a stored procedure and use
it in a query as you have. If that query was in RS itself then it would
work. This is not a RS thing, it is a SQL Server stored procedure issue.
Just try it from Query Analyzer and you will see what I mean. I do the
following, I create
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter|||Try again, sent before done:
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:74D9A9F0-B1F9-4925-8080-87FC99215462@.microsoft.com...
> Hi folks,
> I am describing the issue as below:
> 1. create a stored procedure as below
> ....where age in (@.p_age)
> note: age is the table coumn of table table1 with datatype tinyint
> 2. .... and create a second dataset for parameter @.p_age
> select distinct age from table1
> 3. associate the parameter...run it
> 4. There is no problem with single value. But when two ages are selected,
> I got error message, "...Erro convert data type nvarchar to tinyint"
> Please advise. Peter

Parameter is null, return all?

Hi, heres the problem, ive got a table, and im calling a stored
procedure on the table. Now lets say a row contains the numbers 1
through 5, and i had a parameter in the sp that would be given one of
those values, and then return the corresponding row, but if they it
were passed a null value, how could i make it return all objects?Right
now i have this but i also want to return level 2, 3, 4, 5, etc:
SecurityID = ISNULL(@.SecurityLevel, '1')You can use a WHERE clause like
WHERE (
@.SecurityLevel = SecurityID
OR
@.SecurityLevel IS NULL
)
This will often perform poorly, because there is no one
query plan that can efficiently serve both cases, and another
thing to try is
WHERE (
SecurityID >= COALESCE(@.SecurityLevel,-2147483648)
AND
SecurityID <= COALESCE(@.SecurityLevel,2147483647)
)
(or with other values if the type of SecurityID is not INT).
Steve Kass
Drew University
nbs.tag@.gmail.com wrote:

>Hi, heres the problem, ive got a table, and im calling a stored
>procedure on the table. Now lets say a row contains the numbers 1
>through 5, and i had a parameter in the sp that would be given one of
>those values, and then return the corresponding row, but if they it
>were passed a null value, how could i make it return all objects?Right
>now i have this but i also want to return level 2, 3, 4, 5, etc:
>SecurityID = ISNULL(@.SecurityLevel, '1')
>
>|||SecurityID = @.SecurityLevel OR @.SecurityLevel IS NULL
Also see
http://sommarskog.se/dyn-search.html
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<nbs.tag@.gmail.com> wrote in message
news:1148911602.878618.143780@.j55g2000cwa.googlegroups.com...
> Hi, heres the problem, ive got a table, and im calling a stored
> procedure on the table. Now lets say a row contains the numbers 1
> through 5, and i had a parameter in the sp that would be given one of
> those values, and then return the corresponding row, but if they it
> were passed a null value, how could i make it return all objects?Right
> now i have this but i also want to return level 2, 3, 4, 5, etc:
> SecurityID = ISNULL(@.SecurityLevel, '1')
>

Friday, March 23, 2012

Parameter & Execution plans.

Hi all,

I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.

SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)

SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)

(takes a very long time to retrieve the records)

Thanks in advance,

DBLearner.

Hi DBLearner,

SQL Server recompiles your SQL each time becuase you are running Adhoc SQL. You can either create a stored procedure or use Forced Parameterization database option (sql 2005 ONLY).

The reason its using different access methods could be down to what STATISTICS are stored for those values. You might want to update stats and try it again.

Jag

|||

what all are the indexes created on this table/column. ALso tell us the approx occurence of this column value (if its not PK).

Madhu

Parameter - Set default values - Some fields missing

Please help. I'm using Crytal Reports v.9. I would like to create a parameter and want to set default values. The table I'm using contains 150 fields, however, the Browse Field drop down only shows 22 fields. Is there a way to fix this so that it'll show all 150 fields?

Thanks a lot to all who respond!

- SCham -What is the database you are using?
Why do you want to set default value to 150 columns?

Wednesday, March 21, 2012

Parallel SQL Destination Load Pre-Process error

I cannot find any information on this error. It occurs on packages that are writing to the same table using a sql server destination. I suppose it would be a good exercise in error handling, but I'd rather avoid it.

Error: Unable to prepare the SSIS bulk insert for data insertion.
Error: component "FACT_BillingLineItem Destination" (22652) failed the pre-execute phase and returned error code 0xC0202071.
The cause of these errors, I eventually determined, was that SQL Server/SSIS by default uses up system memory until it goes loopy, unless you tell it not to by configuring the SQL Server "Maximum Server Memory" property.

Parallel SQL Destination Load Pre-Process error

Error: Unable to prepare the SSIS bulk insert for data insertion.
Error: component "FACT_BillingLineItem Destination" (22652) failed the pre-execute phase and returned error code 0xC0202071.The cause of these errors, I eventually determined, was that SQL Server/SSIS by default uses up system memory until it goes loopy, unless you tell it not to by configuring the SQL Server "Maximum Server Memory" property.

Parallel I/O for partitioned table

Hi,
Imagine that I have a partitioned table, half of rows on one disk and the
other half on other disk.
when I query like this:
SELECT * FROM MyTable
Can I expect to get the result faster comparing with the situation that
whole of table resides on one disk? Will these two partitions be scanned in
parallel?
Thanks in advance,
LeilaWhether a parallel plan can be used on a partitioned table depends on the
number of CPUs, query cost, available memory, current workload, and the
sp_configure'd value of Maximum degree of Parallelism (MAXDOP).
===== For more information =====
~ Partitioned tables, parallelism & performance considerations
http://blogs.msdn.com/sqlcat/archiv.../30/498415.aspx
Martin Poon | Microsoft MVP (SQL Server)
http://msmvps.com/blogs/martinpoon
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Imagine that I have a partitioned table, half of rows on one disk and the
> other half on other disk.
> when I query like this:
> SELECT * FROM MyTable
> Can I expect to get the result faster comparing with the situation that
> whole of table resides on one disk? Will these two partitions be scanned
> in parallel?
> Thanks in advance,
> Leila
>|||Just to rephrase Martin's reply: If the optimizer chooses a parallel
query plan you have a good chance of parallel I/O, but no guarantee. If
your server or query is configured to not use parallellism (or is not an
SMP system), or if the optimizer simply does not choose the parallel
query plan (when it thinks it is not worth it), then the standard
sequential query plan will be used. In that case there will definitely
not be parallel I/O.
Gert-Jan
"Martin Poon [MVP]" wrote:[vbcol=seagreen]
> Whether a parallel plan can be used on a partitioned table depends on the
> number of CPUs, query cost, available memory, current workload, and the
> sp_configure'd value of Maximum degree of Parallelism (MAXDOP).
> ===== For more information =====
> ~ Partitioned tables, parallelism & performance considerations
> http://blogs.msdn.com/sqlcat/archiv.../30/498415.aspx
> --
> Martin Poon | Microsoft MVP (SQL Server)
> http://msmvps.com/blogs/martinpoon
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...|||Thanks everybody!
Assuming that I have more than one processor and SQL Server configuration
allows parallelism, will there be difference between a partitioned table
(like mine) and non-partitioned situation?
My focus is on experimenting the advantage of partitioned table vs.
non-partitioned table.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4666F6DD.11919E0D@.toomuchspamalready.nl...[vbcol=seagreen]
> Just to rephrase Martin's reply: If the optimizer chooses a parallel
> query plan you have a good chance of parallel I/O, but no guarantee. If
> your server or query is configured to not use parallellism (or is not an
> SMP system), or if the optimizer simply does not choose the parallel
> query plan (when it thinks it is not worth it), then the standard
> sequential query plan will be used. In that case there will definitely
> not be parallel I/O.
> Gert-Jan
>
> "Martin Poon [MVP]" wrote:|||Leila wrote:
> Thanks everybody!
> Assuming that I have more than one processor and SQL Server configuration
> allows parallelism, will there be difference between a partitioned table
> (like mine) and non-partitioned situation?
If you have a partitioned table with 2 partitiones where each partition
is on a separate disk and you compare it with a non-partitioned table
that is on one disk, then, if the parallel plan is used, there will be
parallel I/O, and the query could be up to 2 times faster.
However, if you compare it to a non-partitioned table that is on two
disks (RAID0), then it would be equally fast for the parallel plan, and
slower for the non-parallel plan. In other words: you would have to tune
your system very intensely (and often) to get better performance than
the same hardware in a striped configuration.

> My focus is on experimenting the advantage of partitioned table vs.
> non-partitioned table.
Partitioning was not primarily created as a performance enhancement. It
was created to make the table more manageble. You can backup (and
restore) per partition, easily add tables and remove partitions, etc.
Gert-Jan|||I would have thought one of a partitioned table's primary benefits would be
enhancing performance. Take 10M rows of 'old' data and have them in an
'archive' partition and the last month's 10K rows in the 'current' partition
and now queries against this table hitting only the last month's data will
have far fewer I/Os (whether index seeks due to less index depth or full
scans for non-index hits) than hitting the entire dataset.
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:466723BD.C6A5FCCF@.toomuchspamalready.nl...
> Leila wrote:
> If you have a partitioned table with 2 partitiones where each partition
> is on a separate disk and you compare it with a non-partitioned table
> that is on one disk, then, if the parallel plan is used, there will be
> parallel I/O, and the query could be up to 2 times faster.
> However, if you compare it to a non-partitioned table that is on two
> disks (RAID0), then it would be equally fast for the parallel plan, and
> slower for the non-parallel plan. In other words: you would have to tune
> your system very intensely (and often) to get better performance than
> the same hardware in a striped configuration.
>
> Partitioning was not primarily created as a performance enhancement. It
> was created to make the table more manageble. You can backup (and
> restore) per partition, easily add tables and remove partitions, etc.
> Gert-Jan|||Lines: 24
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1181233680 news.xs4all.nl 336 [::ffff:194.109.252.208]:1135
X-Complaints-To: abuse@.xs4all.nl
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:36825
TheSQLGuru wrote:
> I would have thought one of a partitioned table's primary benefits would b
e
> enhancing performance. Take 10M rows of 'old' data and have them in an
> 'archive' partition and the last month's 10K rows in the 'current' partiti
on
> and now queries against this table hitting only the last month's data will
> have far fewer I/Os (whether index seeks due to less index depth or full
> scans for non-index hits) than hitting the entire dataset.
Not really, or let's say not necessarily. If the clustered index of the
non-partitioned table is on the date column, then the seek or (partial)
scan will access the same number of leaf pages.
Whether the index depth for the partitioned table is lower depends on
the key width and number of rows. The deeper the index is, the 'harder'
it is to get another level, because of the exponential nature of a
(balanced) binary tree. For example: if the index depth for the
non-partitioned table is 6, the chance that it will decrease to 5 if you
split the table in a few partitions is very small.
Obviously, there would be no (significant) difference for full table
scans (or clustered index scans).
Gert-jan|||I think my typical usage idea for a partitioned table must be different from
yours. That or we will just have to agree to disagree. :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46683211.98EBB46C@.toomuchspamalready.nl...
> TheSQLGuru wrote:
> Not really, or let's say not necessarily. If the clustered index of the
> non-partitioned table is on the date column, then the seek or (partial)
> scan will access the same number of leaf pages.
> Whether the index depth for the partitioned table is lower depends on
> the key width and number of rows. The deeper the index is, the 'harder'
> it is to get another level, because of the exponential nature of a
> (balanced) binary tree. For example: if the index depth for the
> non-partitioned table is 6, the chance that it will decrease to 5 if you
> split the table in a few partitions is very small.
> Obviously, there would be no (significant) difference for full table
> scans (or clustered index scans).
> Gert-jan|||I've had some time to think about it a bit more, and there are some
scenario's where query performance would benefit from partitioning. Not
the scenario's I mentioned, because I think those still hold. But most
likely the scenario's you were suggesting, scenario's that are quite
common.
If you have a query that selects from a particular partition (for
example a date range), and at the same time has a predicate that matches
a nonclustered index (for example amount), then only the nonclustered
index of that particular partition has to be examined, which involves
fewer pages than that same index range on a non-partitioned table.
The partitioned scenario offers benefits here. Normally, on a
non-partitioned table, you would create a nonclustered index on (amount)
and not on ([column representing a partition],amount). You would
probably not even have a column that represents a "virtual partition".
For example, you would probably have a date column, not a column where
"1" represents all dates in 2006, "2" represents all dates after 2006
and "0" represents all dates prior to 2006. And you would not normally
have a predicate in the query that filters this "virtual partition"
column. In addition, the partitioning functionality allows the
administrator to change the partitioning relatively cheaply.
Of course, there is also a downside to partitioning tables, for example
for index seeks. Let's assume partitioning on a date range, and a
nonclustered index on amount. If you select the maximum amount and do
not limit the date range, then the storage engine will have to do an
index seek for each partition. Assuming 3 partitions, this would take 3
times as much reads when compared to the non-partitioned scenario. You
would not want such a query to be running hundreds of times per second.
For full or partial scans, the added cost of scanning more than one
partition will usually not have much impact, unless you have a large
amount of partitions.
Gert-Jan
TheSQLGuru wrote:[vbcol=seagreen]
> I think my typical usage idea for a partitioned table must be different fr
om
> yours. That or we will just have to agree to disagree. :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:46683211.98EBB46C@.toomuchspamalready.nl...

Parallel I/O for partitioned table

Hi,
Imagine that I have a partitioned table, half of rows on one disk and the
other half on other disk.
when I query like this:
SELECT * FROM MyTable
Can I expect to get the result faster comparing with the situation that
whole of table resides on one disk? Will these two partitions be scanned in
parallel?
Thanks in advance,
Leila
Whether a parallel plan can be used on a partitioned table depends on the
number of CPUs, query cost, available memory, current workload, and the
sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).
===== For more information =====
~ Partitioned tables, parallelism & performance considerations
http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
Martin Poon | Microsoft MVP (SQL Server)
http://msmvps.com/blogs/martinpoon
"Leila" <Leilas@.hotpop.com> wrote in message
news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Imagine that I have a partitioned table, half of rows on one disk and the
> other half on other disk.
> when I query like this:
> SELECT * FROM MyTable
> Can I expect to get the result faster comparing with the situation that
> whole of table resides on one disk? Will these two partitions be scanned
> in parallel?
> Thanks in advance,
> Leila
>
|||Just to rephrase Martin's reply: If the optimizer chooses a parallel
query plan you have a good chance of parallel I/O, but no guarantee. If
your server or query is configured to not use parallellism (or is not an
SMP system), or if the optimizer simply does not choose the parallel
query plan (when it thinks it is not worth it), then the standard
sequential query plan will be used. In that case there will definitely
not be parallel I/O.
Gert-Jan
"Martin Poon [MVP]" wrote:[vbcol=seagreen]
> Whether a parallel plan can be used on a partitioned table depends on the
> number of CPUs, query cost, available memory, current workload, and the
> sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).
> ===== For more information =====
> ~ Partitioned tables, parallelism & performance considerations
> http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
> --
> Martin Poon | Microsoft MVP (SQL Server)
> http://msmvps.com/blogs/martinpoon
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
|||Thanks everybody!
Assuming that I have more than one processor and SQL Server configuration
allows parallelism, will there be difference between a partitioned table
(like mine) and non-partitioned situation?
My focus is on experimenting the advantage of partitioned table vs.
non-partitioned table.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4666F6DD.11919E0D@.toomuchspamalready.nl...[vbcol=seagreen]
> Just to rephrase Martin's reply: If the optimizer chooses a parallel
> query plan you have a good chance of parallel I/O, but no guarantee. If
> your server or query is configured to not use parallellism (or is not an
> SMP system), or if the optimizer simply does not choose the parallel
> query plan (when it thinks it is not worth it), then the standard
> sequential query plan will be used. In that case there will definitely
> not be parallel I/O.
> Gert-Jan
>
> "Martin Poon [MVP]" wrote:
|||Leila wrote:
> Thanks everybody!
> Assuming that I have more than one processor and SQL Server configuration
> allows parallelism, will there be difference between a partitioned table
> (like mine) and non-partitioned situation?
If you have a partitioned table with 2 partitiones where each partition
is on a separate disk and you compare it with a non-partitioned table
that is on one disk, then, if the parallel plan is used, there will be
parallel I/O, and the query could be up to 2 times faster.
However, if you compare it to a non-partitioned table that is on two
disks (RAID0), then it would be equally fast for the parallel plan, and
slower for the non-parallel plan. In other words: you would have to tune
your system very intensely (and often) to get better performance than
the same hardware in a striped configuration.

> My focus is on experimenting the advantage of partitioned table vs.
> non-partitioned table.
Partitioning was not primarily created as a performance enhancement. It
was created to make the table more manageble. You can backup (and
restore) per partition, easily add tables and remove partitions, etc.
Gert-Jan
|||I would have thought one of a partitioned table's primary benefits would be
enhancing performance. Take 10M rows of 'old' data and have them in an
'archive' partition and the last month's 10K rows in the 'current' partition
and now queries against this table hitting only the last month's data will
have far fewer I/Os (whether index seeks due to less index depth or full
scans for non-index hits) than hitting the entire dataset.
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:466723BD.C6A5FCCF@.toomuchspamalready.nl...
> Leila wrote:
> If you have a partitioned table with 2 partitiones where each partition
> is on a separate disk and you compare it with a non-partitioned table
> that is on one disk, then, if the parallel plan is used, there will be
> parallel I/O, and the query could be up to 2 times faster.
> However, if you compare it to a non-partitioned table that is on two
> disks (RAID0), then it would be equally fast for the parallel plan, and
> slower for the non-parallel plan. In other words: you would have to tune
> your system very intensely (and often) to get better performance than
> the same hardware in a striped configuration.
>
> Partitioning was not primarily created as a performance enhancement. It
> was created to make the table more manageble. You can backup (and
> restore) per partition, easily add tables and remove partitions, etc.
> Gert-Jan
|||TheSQLGuru wrote:
> I would have thought one of a partitioned table's primary benefits would be
> enhancing performance. Take 10M rows of 'old' data and have them in an
> 'archive' partition and the last month's 10K rows in the 'current' partition
> and now queries against this table hitting only the last month's data will
> have far fewer I/Os (whether index seeks due to less index depth or full
> scans for non-index hits) than hitting the entire dataset.
Not really, or let's say not necessarily. If the clustered index of the
non-partitioned table is on the date column, then the seek or (partial)
scan will access the same number of leaf pages.
Whether the index depth for the partitioned table is lower depends on
the key width and number of rows. The deeper the index is, the 'harder'
it is to get another level, because of the exponential nature of a
(balanced) binary tree. For example: if the index depth for the
non-partitioned table is 6, the chance that it will decrease to 5 if you
split the table in a few partitions is very small.
Obviously, there would be no (significant) difference for full table
scans (or clustered index scans).
Gert-jan
|||I think my typical usage idea for a partitioned table must be different from
yours. That or we will just have to agree to disagree. :-)
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46683211.98EBB46C@.toomuchspamalready.nl...
> TheSQLGuru wrote:
> Not really, or let's say not necessarily. If the clustered index of the
> non-partitioned table is on the date column, then the seek or (partial)
> scan will access the same number of leaf pages.
> Whether the index depth for the partitioned table is lower depends on
> the key width and number of rows. The deeper the index is, the 'harder'
> it is to get another level, because of the exponential nature of a
> (balanced) binary tree. For example: if the index depth for the
> non-partitioned table is 6, the chance that it will decrease to 5 if you
> split the table in a few partitions is very small.
> Obviously, there would be no (significant) difference for full table
> scans (or clustered index scans).
> Gert-jan
|||I've had some time to think about it a bit more, and there are some
scenario's where query performance would benefit from partitioning. Not
the scenario's I mentioned, because I think those still hold. But most
likely the scenario's you were suggesting, scenario's that are quite
common.
If you have a query that selects from a particular partition (for
example a date range), and at the same time has a predicate that matches
a nonclustered index (for example amount), then only the nonclustered
index of that particular partition has to be examined, which involves
fewer pages than that same index range on a non-partitioned table.
The partitioned scenario offers benefits here. Normally, on a
non-partitioned table, you would create a nonclustered index on (amount)
and not on ([column representing a partition],amount). You would
probably not even have a column that represents a "virtual partition".
For example, you would probably have a date column, not a column where
"1" represents all dates in 2006, "2" represents all dates after 2006
and "0" represents all dates prior to 2006. And you would not normally
have a predicate in the query that filters this "virtual partition"
column. In addition, the partitioning functionality allows the
administrator to change the partitioning relatively cheaply.
Of course, there is also a downside to partitioning tables, for example
for index seeks. Let's assume partitioning on a date range, and a
nonclustered index on amount. If you select the maximum amount and do
not limit the date range, then the storage engine will have to do an
index seek for each partition. Assuming 3 partitions, this would take 3
times as much reads when compared to the non-partitioned scenario. You
would not want such a query to be running hundreds of times per second.
For full or partial scans, the added cost of scanning more than one
partition will usually not have much impact, unless you have a large
amount of partitions.
Gert-Jan
TheSQLGuru wrote:[vbcol=seagreen]
> I think my typical usage idea for a partitioned table must be different from
> yours. That or we will just have to agree to disagree. :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:46683211.98EBB46C@.toomuchspamalready.nl...
sql

Parallel I/O for partitioned table

Hi,
Imagine that I have a partitioned table, half of rows on one disk and the
other half on other disk.
when I query like this:
SELECT * FROM MyTable
Can I expect to get the result faster comparing with the situation that
whole of table resides on one disk? Will these two partitions be scanned in
parallel?
Thanks in advance,
LeilaWhether a parallel plan can be used on a partitioned table depends on the
number of CPUs, query cost, available memory, current workload, and the
sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).
===== For more information =====~ Partitioned tables, parallelism & performance considerations
http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
--
Martin Poon | Microsoft MVP (SQL Server)
http://msmvps.com/blogs/martinpoon
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Imagine that I have a partitioned table, half of rows on one disk and the
> other half on other disk.
> when I query like this:
> SELECT * FROM MyTable
> Can I expect to get the result faster comparing with the situation that
> whole of table resides on one disk? Will these two partitions be scanned
> in parallel?
> Thanks in advance,
> Leila
>|||Just to rephrase Martin's reply: If the optimizer chooses a parallel
query plan you have a good chance of parallel I/O, but no guarantee. If
your server or query is configured to not use parallellism (or is not an
SMP system), or if the optimizer simply does not choose the parallel
query plan (when it thinks it is not worth it), then the standard
sequential query plan will be used. In that case there will definitely
not be parallel I/O.
Gert-Jan
"Martin Poon [MVP]" wrote:
> Whether a parallel plan can be used on a partitioned table depends on the
> number of CPUs, query cost, available memory, current workload, and the
> sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).
> ===== For more information =====> ~ Partitioned tables, parallelism & performance considerations
> http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
> --
> Martin Poon | Microsoft MVP (SQL Server)
> http://msmvps.com/blogs/martinpoon
> --
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
> > Hi,
> > Imagine that I have a partitioned table, half of rows on one disk and the
> > other half on other disk.
> > when I query like this:
> >
> > SELECT * FROM MyTable
> >
> > Can I expect to get the result faster comparing with the situation that
> > whole of table resides on one disk? Will these two partitions be scanned
> > in parallel?
> > Thanks in advance,
> > Leila
> >
> >|||Thanks everybody!
Assuming that I have more than one processor and SQL Server configuration
allows parallelism, will there be difference between a partitioned table
(like mine) and non-partitioned situation?
My focus is on experimenting the advantage of partitioned table vs.
non-partitioned table.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4666F6DD.11919E0D@.toomuchspamalready.nl...
> Just to rephrase Martin's reply: If the optimizer chooses a parallel
> query plan you have a good chance of parallel I/O, but no guarantee. If
> your server or query is configured to not use parallellism (or is not an
> SMP system), or if the optimizer simply does not choose the parallel
> query plan (when it thinks it is not worth it), then the standard
> sequential query plan will be used. In that case there will definitely
> not be parallel I/O.
> Gert-Jan
>
> "Martin Poon [MVP]" wrote:
>> Whether a parallel plan can be used on a partitioned table depends on the
>> number of CPUs, query cost, available memory, current workload, and the
>> sp_configure'd value of Maximum Degree of Parallelism (MAXDOP).
>> ===== For more information =====>> ~ Partitioned tables, parallelism & performance considerations
>> http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
>> --
>> Martin Poon | Microsoft MVP (SQL Server)
>> http://msmvps.com/blogs/martinpoon
>> --
>> "Leila" <Leilas@.hotpop.com> wrote in message
>> news:uA0kcY7pHHA.3512@.TK2MSFTNGP06.phx.gbl...
>> > Hi,
>> > Imagine that I have a partitioned table, half of rows on one disk and
>> > the
>> > other half on other disk.
>> > when I query like this:
>> >
>> > SELECT * FROM MyTable
>> >
>> > Can I expect to get the result faster comparing with the situation that
>> > whole of table resides on one disk? Will these two partitions be
>> > scanned
>> > in parallel?
>> > Thanks in advance,
>> > Leila
>> >
>> >|||Leila wrote:
> Thanks everybody!
> Assuming that I have more than one processor and SQL Server configuration
> allows parallelism, will there be difference between a partitioned table
> (like mine) and non-partitioned situation?
If you have a partitioned table with 2 partitiones where each partition
is on a separate disk and you compare it with a non-partitioned table
that is on one disk, then, if the parallel plan is used, there will be
parallel I/O, and the query could be up to 2 times faster.
However, if you compare it to a non-partitioned table that is on two
disks (RAID0), then it would be equally fast for the parallel plan, and
slower for the non-parallel plan. In other words: you would have to tune
your system very intensely (and often) to get better performance than
the same hardware in a striped configuration.
> My focus is on experimenting the advantage of partitioned table vs.
> non-partitioned table.
Partitioning was not primarily created as a performance enhancement. It
was created to make the table more manageble. You can backup (and
restore) per partition, easily add tables and remove partitions, etc.
Gert-Jan|||I would have thought one of a partitioned table's primary benefits would be
enhancing performance. Take 10M rows of 'old' data and have them in an
'archive' partition and the last month's 10K rows in the 'current' partition
and now queries against this table hitting only the last month's data will
have far fewer I/Os (whether index seeks due to less index depth or full
scans for non-index hits) than hitting the entire dataset.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:466723BD.C6A5FCCF@.toomuchspamalready.nl...
> Leila wrote:
>> Thanks everybody!
>> Assuming that I have more than one processor and SQL Server configuration
>> allows parallelism, will there be difference between a partitioned table
>> (like mine) and non-partitioned situation?
> If you have a partitioned table with 2 partitiones where each partition
> is on a separate disk and you compare it with a non-partitioned table
> that is on one disk, then, if the parallel plan is used, there will be
> parallel I/O, and the query could be up to 2 times faster.
> However, if you compare it to a non-partitioned table that is on two
> disks (RAID0), then it would be equally fast for the parallel plan, and
> slower for the non-parallel plan. In other words: you would have to tune
> your system very intensely (and often) to get better performance than
> the same hardware in a striped configuration.
>> My focus is on experimenting the advantage of partitioned table vs.
>> non-partitioned table.
> Partitioning was not primarily created as a performance enhancement. It
> was created to make the table more manageble. You can backup (and
> restore) per partition, easily add tables and remove partitions, etc.
> Gert-Jan|||TheSQLGuru wrote:
> I would have thought one of a partitioned table's primary benefits would be
> enhancing performance. Take 10M rows of 'old' data and have them in an
> 'archive' partition and the last month's 10K rows in the 'current' partition
> and now queries against this table hitting only the last month's data will
> have far fewer I/Os (whether index seeks due to less index depth or full
> scans for non-index hits) than hitting the entire dataset.
Not really, or let's say not necessarily. If the clustered index of the
non-partitioned table is on the date column, then the seek or (partial)
scan will access the same number of leaf pages.
Whether the index depth for the partitioned table is lower depends on
the key width and number of rows. The deeper the index is, the 'harder'
it is to get another level, because of the exponential nature of a
(balanced) binary tree. For example: if the index depth for the
non-partitioned table is 6, the chance that it will decrease to 5 if you
split the table in a few partitions is very small.
Obviously, there would be no (significant) difference for full table
scans (or clustered index scans).
Gert-jan|||I think my typical usage idea for a partitioned table must be different from
yours. That or we will just have to agree to disagree. :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:46683211.98EBB46C@.toomuchspamalready.nl...
> TheSQLGuru wrote:
>> I would have thought one of a partitioned table's primary benefits would
>> be
>> enhancing performance. Take 10M rows of 'old' data and have them in an
>> 'archive' partition and the last month's 10K rows in the 'current'
>> partition
>> and now queries against this table hitting only the last month's data
>> will
>> have far fewer I/Os (whether index seeks due to less index depth or full
>> scans for non-index hits) than hitting the entire dataset.
> Not really, or let's say not necessarily. If the clustered index of the
> non-partitioned table is on the date column, then the seek or (partial)
> scan will access the same number of leaf pages.
> Whether the index depth for the partitioned table is lower depends on
> the key width and number of rows. The deeper the index is, the 'harder'
> it is to get another level, because of the exponential nature of a
> (balanced) binary tree. For example: if the index depth for the
> non-partitioned table is 6, the chance that it will decrease to 5 if you
> split the table in a few partitions is very small.
> Obviously, there would be no (significant) difference for full table
> scans (or clustered index scans).
> Gert-jan|||I've had some time to think about it a bit more, and there are some
scenario's where query performance would benefit from partitioning. Not
the scenario's I mentioned, because I think those still hold. But most
likely the scenario's you were suggesting, scenario's that are quite
common.
If you have a query that selects from a particular partition (for
example a date range), and at the same time has a predicate that matches
a nonclustered index (for example amount), then only the nonclustered
index of that particular partition has to be examined, which involves
fewer pages than that same index range on a non-partitioned table.
The partitioned scenario offers benefits here. Normally, on a
non-partitioned table, you would create a nonclustered index on (amount)
and not on ([column representing a partition],amount). You would
probably not even have a column that represents a "virtual partition".
For example, you would probably have a date column, not a column where
"1" represents all dates in 2006, "2" represents all dates after 2006
and "0" represents all dates prior to 2006. And you would not normally
have a predicate in the query that filters this "virtual partition"
column. In addition, the partitioning functionality allows the
administrator to change the partitioning relatively cheaply.
Of course, there is also a downside to partitioning tables, for example
for index seeks. Let's assume partitioning on a date range, and a
nonclustered index on amount. If you select the maximum amount and do
not limit the date range, then the storage engine will have to do an
index seek for each partition. Assuming 3 partitions, this would take 3
times as much reads when compared to the non-partitioned scenario. You
would not want such a query to be running hundreds of times per second.
For full or partial scans, the added cost of scanning more than one
partition will usually not have much impact, unless you have a large
amount of partitions.
Gert-Jan
TheSQLGuru wrote:
> I think my typical usage idea for a partitioned table must be different from
> yours. That or we will just have to agree to disagree. :-)
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:46683211.98EBB46C@.toomuchspamalready.nl...
> > TheSQLGuru wrote:
> >>
> >> I would have thought one of a partitioned table's primary benefits would
> >> be
> >> enhancing performance. Take 10M rows of 'old' data and have them in an
> >> 'archive' partition and the last month's 10K rows in the 'current'
> >> partition
> >> and now queries against this table hitting only the last month's data
> >> will
> >> have far fewer I/Os (whether index seeks due to less index depth or full
> >> scans for non-index hits) than hitting the entire dataset.
> >
> > Not really, or let's say not necessarily. If the clustered index of the
> > non-partitioned table is on the date column, then the seek or (partial)
> > scan will access the same number of leaf pages.
> >
> > Whether the index depth for the partitioned table is lower depends on
> > the key width and number of rows. The deeper the index is, the 'harder'
> > it is to get another level, because of the exponential nature of a
> > (balanced) binary tree. For example: if the index depth for the
> > non-partitioned table is 6, the chance that it will decrease to 5 if you
> > split the table in a few partitions is very small.
> >
> > Obviously, there would be no (significant) difference for full table
> > scans (or clustered index scans).
> >
> > Gert-jan

Tuesday, March 20, 2012

Parallel execution in SQL Server

Is there any way to run a stored procedure in parallel to another one? i.e. I have a stored procedure that sends an email. I then scan a table and send any unsent emails. I do not want the second part to slow the response to the user.

Why don't make a JOB for send any unsend emails ?

Store procedure only insert a row in a buffer table and job consume the rows in that table.

Wink

|||I have tried using a Job before and it was not a nice experience - guess I will have to sort it out properly this time.

parallel execution

Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it

Regards
MThe easiest way is probably to have 3 copies of your Data Flow, each using a different File Connection Manager, and three copies of your File Connection Manager, each for a different file. You will also need to uncheck the "Table Lock" option on the destinations. If there are no precedences defined for your Data Flows, they will execute in parallel. Ugly, but simple.
There used to be a parallel execution option on the Loop task that would have allowed you to do this with one Data Flow, but it was removed.
It is also possible to use a master package that executes your current package three times in parallel, thus avoiding the Data Flow duplication above, but you'll face the challenge of having each instance of the package open a separate file. You would need to have three different Package Configuration files to define different values for a parameter that you would use in an Expression to set the ConnectionString property on your File Connection Manager. Complicated, but probably a superior design if you're up for it. You'll still need to turn off table locking.
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.|||
Note that you'll need multiple processors to make this a worthwhile endeavor. Also, if your files are very large tempdb will suffer without the table locking.

Hey Jay,
Is it mandatory to have multiple processors for parallel execution for the above mentioned issue.
I have done all the above things that you have suggested, but it stills seems that the loading is happening sequentially. Please note that the my machine is single processor machine.

Regards
Meghana|||It shouldn't be mandatory, but I wouldn't expect you to see any benefits without multiple processors. I ran a test on a single processor machine and did not have any difficulty getting my Data Flows to run concurrently. I also discovered that disabling the table locking is not necessary.
One thing to check is the MaxConcurrentExecutables property. The only way I could force my package to run sequentially is to set this value to 1. The default value is -1, which means number of processors plus two, so it should let you load three files simultaneously.
I also tested performance. Using a 100k line flat file going directly into a local SQL table, I saw a 10% decrease in performance with two parallel files and 34% decrease with three.
|||

mmhaise wrote:
Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it
Regards
M

Have you tried using a MULTIFLATFILE connection manager? This will enable you to load data from all of the files using just a single data-flow (which is executed just once).
If the metadata of the 3 files is identical this should work very nicely.

If the metadata is different then you can use 2 or 3 Derived Column Transforms to make the pipeline metadata identical and then use a UNION ALL transform to combine the 3 data-paths into one. Again, this is a single data-flow that you execute just the once.
If you are intent on loading the 3 seperately then executing a package 3 times as Jay suggested would be the "cleanest" approach because you can reuse functionality rather than having 3 seperate but identical data-flows.

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

I have another package with four separate file connection managers and data flows; however, I thought it would be nice to use the MULTIFLATFILE since the files have identical metadata. We require them to load in parallel.

parallel execution

mmhaise wrote:
Hi
I have 3 sources as Flat files and the destination is a single table. Is it possible for running a package so that it is executed parallelly. I mean , all the 3 files should run at the same time and load it into the destination table.
If it is possible, please let me know how to do it
Regards
M

I would definately look into using the MULTIFLATFILE connection manager though if I were you.

-Jamie|||

Is it possible to make the MULTIFLATFILE connection load in parallel? I have tried using one to load 4 csv file in parallel to the same destination table. The data flow is loading them sequentially.

parallel data load using bcp

i did a parallel load into a single table using bcp from 2 machines. as
suggested in the docs i used the TABLOCK hint. as a result i observed that:
a) first all the data from machine1 is inserted. When this is finished then
all the data from machine2 is inserted
b) i don't see any parallelism in this . Is there any other way of acheiving
true paralellism.
NOTE: SQL Server is running on a dual cpu machine
--
Vivek T S
Member Technical Staff (Inucom)Did you satisfy all the other requirements for parallel bulk loading?
Database is using BULK_LOGGED or SIMPLE recovery model and the destination
table does not have any indexes?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:409D8E47-D422-48C7-B076-0A9CAE5D39AE@.microsoft.com...
> i did a parallel load into a single table using bcp from 2 machines. as
> suggested in the docs i used the TABLOCK hint. as a result i observed
> that:
> a) first all the data from machine1 is inserted. When this is finished
> then
> all the data from machine2 is inserted
> b) i don't see any parallelism in this . Is there any other way of
> acheiving
> true paralellism.
> NOTE: SQL Server is running on a dual cpu machine
> --
> Vivek T S
> Member Technical Staff (Inucom)|||iam using Simple recovery model. the target table does not have any indexes
(and is empty). what are the other requirements for parallel bulk loading ?
"Gert E.R. Drapers" wrote:
> Did you satisfy all the other requirements for parallel bulk loading?
> Database is using BULK_LOGGED or SIMPLE recovery model and the destination
> table does not have any indexes?
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright Â© SQLDev.Net 1991-2004 All rights reserved.
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:409D8E47-D422-48C7-B076-0A9CAE5D39AE@.microsoft.com...
> >
> > i did a parallel load into a single table using bcp from 2 machines. as
> > suggested in the docs i used the TABLOCK hint. as a result i observed
> > that:
> > a) first all the data from machine1 is inserted. When this is finished
> > then
> > all the data from machine2 is inserted
> > b) i don't see any parallelism in this . Is there any other way of
> > acheiving
> > true paralellism.
> >
> > NOTE: SQL Server is running on a dual cpu machine
> > --
> > Vivek T S
> > Member Technical Staff (Inucom)
>
>|||This ought to work. Are you using the version of BCP that shipped with SQL
2000?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:3728504E-A17E-414A-BF02-3CF45291C45D@.microsoft.com...
> iam using Simple recovery model. the target table does not have any
> indexes
> (and is empty). what are the other requirements for parallel bulk loading
> ?
> "Gert E.R. Drapers" wrote:
>> Did you satisfy all the other requirements for parallel bulk loading?
>> Database is using BULK_LOGGED or SIMPLE recovery model and the
>> destination
>> table does not have any indexes?
>> GertD@.SQLDev.Net
>> Please reply only to the newsgroups.
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> You assume all risk for your use.
>> Copyright © SQLDev.Net 1991-2004 All rights reserved.
>> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
>> news:409D8E47-D422-48C7-B076-0A9CAE5D39AE@.microsoft.com...
>> >
>> > i did a parallel load into a single table using bcp from 2 machines. as
>> > suggested in the docs i used the TABLOCK hint. as a result i observed
>> > that:
>> > a) first all the data from machine1 is inserted. When this is finished
>> > then
>> > all the data from machine2 is inserted
>> > b) i don't see any parallelism in this . Is there any other way of
>> > acheiving
>> > true paralellism.
>> >
>> > NOTE: SQL Server is running on a dual cpu machine
>> > --
>> > Vivek T S
>> > Member Technical Staff (Inucom)
>>|||Look up "minimally logged bulk copy" in BOL for the requirements.
--
Andrew J. Kelly SQL MVP
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:3728504E-A17E-414A-BF02-3CF45291C45D@.microsoft.com...
> iam using Simple recovery model. the target table does not have any
indexes
> (and is empty). what are the other requirements for parallel bulk loading
?
> "Gert E.R. Drapers" wrote:
> > Did you satisfy all the other requirements for parallel bulk loading?
> > Database is using BULK_LOGGED or SIMPLE recovery model and the
destination
> > table does not have any indexes?
> >
> > GertD@.SQLDev.Net
> >
> > Please reply only to the newsgroups.
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > You assume all risk for your use.
> > Copyright © SQLDev.Net 1991-2004 All rights reserved.
> >
> > "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> > news:409D8E47-D422-48C7-B076-0A9CAE5D39AE@.microsoft.com...
> > >
> > > i did a parallel load into a single table using bcp from 2 machines.
as
> > > suggested in the docs i used the TABLOCK hint. as a result i observed
> > > that:
> > > a) first all the data from machine1 is inserted. When this is finished
> > > then
> > > all the data from machine2 is inserted
> > > b) i don't see any parallelism in this . Is there any other way of
> > > acheiving
> > > true paralellism.
> > >
> > > NOTE: SQL Server is running on a dual cpu machine
> > > --
> > > Vivek T S
> > > Member Technical Staff (Inucom)
> >
> >
> >

Paradox table import in SQL Server 2000

I need to import any paradox tables in SQL Server 2000, but i'm having
troubles with char conversion:
Does anyone have any idea about why all accented chars in source paradox
table turn into other chars in the new SQL Table:
in paradox turns into in SQL
" " " " " "
" " " " " "
" " " " ? " "
" " " " " "
" " " " " "
Any hints on what should i trim to correcty this behavior?
Thanks
Mario
you need to pick another collation. What collation is DTS automatically
selecting? It looks like you should be using French.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:cguq57$vv4$1@.news.ngi.it...
> I need to import any paradox tables in SQL Server 2000, but i'm having
> troubles with char conversion:
> Does anyone have any idea about why all accented chars in source paradox
> table turn into other chars in the new SQL Table:
> in paradox turns into in SQL
> " " " " " "
> " " " " " "
> " " " " ? " "
> " " " " " "
> " " " " " "
> Any hints on what should i trim to correcty this behavior?
> Thanks
> Mario
>
|||Hello Hilary,
i couldn't find a way to see which collation is my DTS automatically
selecting, i can only imagine it's Latin General since i've changed nothing
in default settings and i'm in italy.
Anyway it looks more like it's a problem with settings on OEM/ANSI
translation settings, but even in this case i couldn't find a way to set
such a setting.
Mario Cappa
"Hilary Cotter" <hilaryk@.att.net> ha scritto nel messaggio
news:ezjkYZojEHA.3724@.TK2MSFTNGP11.phx.gbl...
> you need to pick another collation. What collation is DTS automatically
> selecting? It looks like you should be using French.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:cguq57$vv4$1@.news.ngi.it...
>
|||how are you importing the table? Is this through a DSN? If so what collation
sequence are you using. I think you should be using ANSI.
Also check the collation of the table which DTS created. Do a sp_help
TableName using Query Analyzer.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:cgvh2s$848$1@.news.ngi.it...
> Hello Hilary,
> i couldn't find a way to see which collation is my DTS automatically
> selecting, i can only imagine it's Latin General since i've changed
nothing[vbcol=seagreen]
> in default settings and i'm in italy.
> Anyway it looks more like it's a problem with settings on OEM/ANSI
> translation settings, but even in this case i couldn't find a way to set
> such a setting.
> Mario Cappa
> "Hilary Cotter" <hilaryk@.att.net> ha scritto nel messaggio
> news:ezjkYZojEHA.3724@.TK2MSFTNGP11.phx.gbl...
paradox
>
|||I'm using ANSI on SQL but i'm not sure the setting is the same on Paradox
table
Table is imported through Paradox 5.x OLE DB drivers and collation sequence
results as Latin1_General_CI_AS
"Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
news:Our0dmqjEHA.556@.tk2msftngp13.phx.gbl...
> how are you importing the table? Is this through a DSN? If so what
collation[vbcol=seagreen]
> sequence are you using. I think you should be using ANSI.
> Also check the collation of the table which DTS created. Do a sp_help
> TableName using Query Analyzer.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:cgvh2s$848$1@.news.ngi.it...
> nothing
automatically[vbcol=seagreen]
having
> paradox
>
|||how about trying the international setting on your dsn?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mario Cappa" <mario.cappa@.arcott.it> wrote in message
news:ezhKiuqjEHA.704@.TK2MSFTNGP09.phx.gbl...
> I'm using ANSI on SQL but i'm not sure the setting is the same on Paradox
> table
> Table is imported through Paradox 5.x OLE DB drivers and collation
sequence[vbcol=seagreen]
> results as Latin1_General_CI_AS
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
> news:Our0dmqjEHA.556@.tk2msftngp13.phx.gbl...
> collation
set
> automatically
> having
>
|||I solved my problems after i could find paradox tables native language and
change it to Western Europe ANSI
Thanky you for your suggestions
Mario
"Hilary Cotter" <hilary.cotter@.gmail.com> ha scritto nel messaggio
news:%23HjsoMwjEHA.2340@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> how about trying the international setting on your dsn?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mario Cappa" <mario.cappa@.arcott.it> wrote in message
> news:ezhKiuqjEHA.704@.TK2MSFTNGP09.phx.gbl...
Paradox[vbcol=seagreen]
> sequence
automatically
> set
>

Paging, Performance and ADODB

I want to do paging with my VB.NET app. I have a large with table
over 5 million records. I've read numerous articles on how to page
using TOP, ROW_COUNT, etc. I've tried the examples that they have
provided. Performance is fine if you are paging the "top" part of the
data set.
However, if I want to go to the last "page" of my data set, and
traverse "backwards" though it, performance is terrible. In my tests,
I have been returning 10 records a page. When I try to traverse
backwards, the best I can get is the 10 records returning in 10+
seconds for each page.
How can I do this efficiently?Oooops, forgot to mention the ADODB part.
I wrote some ADODB code in a test VB.NET app. I used a Recordset to
basically do the same thing that the paging was doing. The page of
results returned back in milliseconds for both the "front" and "back"
end of the data set.
How can I get this kind of performance? I don't want to use ADODB for
doing this.|||I meant to say ROW_NUMBER and OVER rather than ROW_COUNT in my first
post. Sorry for so many posts.

Monday, March 12, 2012

Paging with Gridview and ObjectDataSource

I'm trying to effecinty page through many rows of data with the gridview and objectdatasource. I'm having trouble. I'm using a table adapter with predefined counting and select methods. I have tested all the methods and they all work properly. But when I configure the object datasource to use the table adapter, and set the gridviews datasrouce, the page doesn't load and I wind up getting "time out". Any help?

<asp:GridView ID="GridView1" runat="server" AllowPaging="True" DataSourceID="objTopics">
<Columns>
<asp:BoundField DataField="topic_title" />
</Columns>
<EmptyDataTemplate>
<p>NOTHING HERE</p>
</EmptyDataTemplate>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" EnablePaging="True" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetTopics" SelectCountMethod="GetTopicsRowCount" TypeName="TopicsTableAdapters.discussions_GetTopicsSubSetTableAdapter">
<SelectParameters>
<asp:Parameter DefaultValue="1" Name="startRowIndex" Type="Int32" />
<asp:Parameter DefaultValue="10" Name="maximumRows" Type="Int32" />
<asp:Parameter DefaultValue="1" Name="board_id" Type="Int32" />
</SelectParameters>
</asp:ObjectDataSource>

<asp:GridViewID="gvResult"

runat="server"AutoGenerateColumns="False"CssClass="dataGrid"

OnSelectedIndexChanged="GridView1_SelectedIndexChanged"OnRowDataBound="gvResult_RowDataBound"AllowPaging="True"AllowSorting="True"DataSourceID="odsObjectDataSource"OnPageIndexChanged="gvResult_PageIndexChanged"PageSize="5">

<asp:ButtonFieldCommandName="Select"HeaderText="Select"Text="<img src='../IMAGES/select.jpg'/>"/>

<asp:BoundFieldDataField="SocieteId"HeaderText="Identifiant"ReadOnly="True"/>

<asp:BoundFieldDataField="SocieteLocalite"HeaderText="Localité"ReadOnly="True"/>

</Columns>

<AlternatingRowStyleCssClass="alt"/>

<PagerSettingsMode="NextPreviousFirstLast"/>

</asp:GridView>

<asp:LabelID="lblError"runat="server"Text=""></asp:Label></div>

<asp:ObjectDataSourceID="odsObjectDataSource"runat="server"SelectMethod="GetResults"TypeName="ResultsList">

<asp:ParameterDefaultValue="0"Name="StartRow"Type="Int32"/>

<asp:SessionParameterName="xslSearch"SessionField="xsltPathShowRezults"Type="String"/>

</SelectParameters>

</asp:ObjectDataSource>

For the object datasource you have to writye a class wich will get data, like

privateDataView GetData(int StartRow,string xslSearch)

{

//your code here

return PagedResultsTable.DefaultView;

}

and this method is set in the design for the object datasource.

Paging Technique

Questoin

I am using Sql Server 2000.

I have a table named Cities which has more than 2600000 records.

I have to display the records for a specific city page wise.

I don't want to compromise with performance.

Can anyone has the idea?

Waiting for your fruitful response.

Happy Day And Night For All

Muhammad Zeeshanuddin Khan

Hi Muhammad,

Check out this article. The idea behind is that you only fetch the records from the database that you display on the active pageIndex.

Succes!

Rutger van Hagen

Paging query without using stored procedure

Hello, my table is clustered according to the date. Has anyone found an efficient way to page through 16 million rows of data? The query that I have takes waaaay too long. It is really fast when I page through information at the beginning but when someone tries to access the 9,000th page sometimes it has a timeout error. I am using sql server 2005 let me know if you have any ideas. Thanks

I am also thinking about switch datavase software to something that can handle that many rows. Let me know if you have a suggestion on a particular software that can handle paging through 16 million rows of data.

Hi~

You may take a look at this:

Efficiently Paging Through Large Amounts of Data (VB)

Efficiently Paging Through Large Amounts of Data(C#)

Hope this helps.

Wednesday, March 28, 2012

Parameter name Questions in SqlDataSource??

Parameter multi-value problem when using a stored procedure

Parameter is null, return all?

Friday, March 23, 2012

Parameter & Execution plans.

Parameter - Set default values - Some fields missing

Wednesday, March 21, 2012

Parallel SQL Destination Load Pre-Process error

Parallel SQL Destination Load Pre-Process error

Parallel I/O for partitioned table

Parallel I/O for partitioned table

Parallel I/O for partitioned table

Tuesday, March 20, 2012

Parallel execution in SQL Server

parallel execution

parallel execution

parallel data load using bcp

parallel data load using bcp

Paradox table import in SQL Server 2000

Paging, Performance and ADODB

Monday, March 12, 2012

Paging with Gridview and ObjectDataSource

Paging Technique

Paging query without using stored procedure

Page Numbers in Excel

Blog Archive

About Me