Access ADP integration?

I'm sorry but Management Studio is slow, cumbersome and difficult to
quickly make changes while designing a new system. It is directly and
very visibly impacting my teams production. The same can be said for
developing databases though the Visual Studio IDE.
We are at our highest production when using Access ADP projects to
modify the database. It's intuitive, familiar and very responsive. Do
we have to wait until Office 12 before we have Access ADP / SQL 2005
integration?
Please PLEASE don't say this is not going to happen. We are highly
dependent on this and have alot of infrasture in place based on this
setup.
CI would like to expand on my dislike of Management Studio. There is no
copy / paste for tables or views. There is in Access. There is no
Search / Replace in the SQL window. You have to copy all to a text
editor to search and replace.
But I do really like the View Dependancies option though.
C|||Hi
Well, do you have SQL Server 2005 installled as a named instance? I you do
, so a named instance is consumed memory which amy hurt a performance. Also
check out whether you have AutoClose of the database option checked.
In order to make 'copy /paste' as you like in Access you will have to learn
some T-SQL commands as
SELECT * INTO NewTable FROM OldTable
In addition you have a great option in my opinon as Script Table As to make
changes and DML operations

> Search / Replace in the SQL window. You have to copy all to a text
> editor to search and replace.
Have you tried CTRL-F option in the Query Builder?
"The Cornjerker" <addoty@.gmail.com> wrote in message
news:1140311969.961191.275270@.g47g2000cwa.googlegroups.com...
>I would like to expand on my dislike of Management Studio. There is no
> copy / paste for tables or views. There is in Access. There is no
> Search / Replace in the SQL window. You have to copy all to a text
> editor to search and replace.
> But I do really like the View Dependancies option though.
> C
>|||Uri,
Thanks for helping. I'm trying hard to work with Management Studio. I
know I can do anything with SQL commands, but I think the point of
having a tool like Management Studio is to make things visual and
easier. It just doesn't seem very intuitive for rapid development.
CTRL-F is grayed out for me. I can't seem to find the AutoClose option
plus I'm not for sure what it does.
I guess my frustration comes from the fact that I'm constantly being
forced to change my development process. I'm spending more and more my
time and my development team's time learning new processes to do
basically the same thing. VB6 to VB.NET, ASP to ASP.NET, FrontPage to
Visual Studio.NET, now Access ADP to Management Studio. The products
we produce are basically the same, they just take longer to develop and
seem more difficult to maintain. I think there is something to be said
about incrementally improving technologies instead of these huge
revolutionary leaps and then abandoning previous technologies.
Sorry for the rant,
C

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause