Francisco Tapia
fhtapia at gmail.com
Wed Aug 31 11:07:33 CDT 2011
I wrote up a script that leverages another developer's solution, I co-wrote
this article with Susan Harkins...
http://www.devx.com/dbzone/Article/42340/1954?pf=true
maybe you can extend or improve on what i've done to help leverage, I'm not
sure if the link will prompt you to have a free login account (i may have my
browser cookies saved, if so I'll re-post the script here)
http://www.devx.com/dbzone/Article/42340/1954/1763?supportItem=1
-Francisco
http://bit.ly/sqlthis | Tsql and More...
<http://db.tt/JeXURAx>
On Wed, Aug 31, 2011 at 8:56 AM, Mark Breen <marklbreen at gmail.com> wrote:
> Hello Francisco,
>
> Thank you for your time to respond.
>
> That is extremely interesting and happens to coincide with some sql that I
> am working on currently.
>
> I have a few sprocs that search through all tables, all columns, all
> schemas
> for some text and uses a temp table for temp storage on each iteration.
>
> I did not yet see how to avoid using a cursor, so the temp table is used
> each time I loop.
>
> Currently the routine takes 4 hours and I had to return my intel i7 to
> standard settings because it was reaching 90 degrees when all cores were
> maxed out!
>
> I can do some timings so when I do, I will post the results.
>
> thanks again,
> Mark
>
>
>
>
> On 31 August 2011 16:43, Francisco Tapia <fhtapia at gmail.com> wrote:
>
> > Variable tables are generally tables in memory and are meant to be light
> so
> > that they can survive in memory. A major advantage is you can convert
> > comma
> > delimited lists to variable tables (generally a small-ish table) and have
> > all the benefits of a normal table but in RAM. Temp tables if small will
> > remain in memory, but if the server is busy with other major tasks that
> > demand memory, your temp table will be written to disk. In general a
> > placeholder is always created, but your data may just be in RAM vs on
> disk,
> > my recommendation for people using variable tables or temp tables is that
> > if
> > you're going to be hauling a lot of data (or potentially a lot of data)
> > then
> > you should architect your solution with temp tables, otherwise for
> smaller
> > datasets a variable table is a great performance option.
> >
> >
> > -Francisco
> > http://bit.ly/sqlthis | Tsql and More...
> > <http://db.tt/JeXURAx>
> >
> >
> >
> >
> > On Wed, Aug 31, 2011 at 8:36 AM, Mark Breen <marklbreen at gmail.com>
> wrote:
> >
> > > Hi David,
> > >
> > > what is the difference between Temp table and using a variable tables.
> I
> > > never used such an exotic thing ?
> > >
> > > thanks
> > >
> > > Mark
> > >
> > >
> > > On 30 August 2011 20:42, newsgrps <newsgrps at dalyn.co.nz> wrote:
> > >
> > > > Thanks Francisco. That worked and makes me more comfortable.
> > > >
> > > > David
> > > >
> > > >
> > > > At 31/08/2011, Francisco Tapia wrote:
> > > >
> > > >> i'm not sure, but i've always used an alias when I join my temp
> tables
> > > >> including variable tables, so my change would read
> > > >>
> > > >> FROM @ttmpWeeks AS tWeeks
> > > >>
> > > >> then your ON statment can read tWeeks.WeekNo = join clause
> > > >>
> > > >> -Francisco
> > > >> http://bit.ly/sqlthis | Tsql and More...
> > > >> <http://db.tt/JeXURAx>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >> On Tue, Aug 30, 2011 at 1:41 AM, newsgrps <newsgrps at dalyn.co.nz>
> > wrote:
> > > >>
> > > >> > Group,
> > > >> >
> > > >> > Can someone please explain why this is
> > > >> >
> > > >> > I get an error "Must declare the variable ttmpWeeks" when I try to
> > > save
> > > >> the
> > > >> > following in a stored procedure:
> > > >> >
> > > >> > ALTER PROCEDURE sprptProductionSummary
> > > >> > (
> > > >> > @txtDate1 varchar(20),
> > > >> > @txtDate2 varchar(20)
> > > >> > )
> > > >> > AS
> > > >> > SET NOCOUNT ON
> > > >> >
> > > >> > SET DATEFIRST 1 -- Monday
> > > >> >
> > > >> > DECLARE @ttmpWeeks table (WeekNo tinyint)
> > > >> >
> > > >> > DECLARE @tmpDate as datetime
> > > >> >
> > > >> > SET @tmpDate = @txtDate1
> > > >> >
> > > >> > INSERT INTO @ttmpWeeks (WeekNo)
> > > >> > VALUES (DATEPART(week, @tmpDate))
> > > >> >
> > > >> > SET @tmpDate = DATEADD(week, 1, @tmpDate)
> > > >> >
> > > >> > WHILE @tmpdate < @txtDate2
> > > >> > BEGIN
> > > >> > INSERT INTO @ttmpWeeks (WeekNo)
> > > >> > VALUES (DATEPART(week, @tmpDate))
> > > >> >
> > > >> > SET @tmpDate = DATEADD(week, 1, @tmpDate)
> > > >> > END
> > > >> >
> > > >> > SELECT WeekNo, ISNULL(spKnit.KnittedWeight,0) AS
> > KnittedWeight
> > > >> > FROM @ttmpWeeks LEFT OUTER JOIN (SELECT DATEPART(week,
> > > >> > dbo.tblRoll.KnitDate) AS WeekNum, ISNULL(SUM(KnitWeight),0) AS
> > > >> KnittedWeight
> > > >> > FROM dbo.tblRoll
> > > >> > WHERE KnitDate
> > BETWEEN
> > > >> > @txtDate1 AND @txtDate2
> > > >> > GROUP BY
> > DATEPART(week,
> > > >> > dbo.tblRoll.KnitDate)) AS spKnit ON @ttmpWeeks.WeekNo =
> > spKnit.WeekNum
> > > >> >
> > > >> > {rest of code continues}
> > > >> >
> > > >> > However, if I change the last line to
> > > >> > GROUP BY DATEPART(week, dbo.tblRoll.KnitDate)) AS spKnit ON WeekNo
> =
> > > >> > spKnit.WeekNum
> > > >> > it saves and works.
> > > >> >
> > > >> > Why doesn't it accept the table part of the WeekNo field?
> > > >> >
> > > >> >
> > > >> > Regards
> > > >> >
> > > >> > David Emerson
> > > >> > Dalyn Software Ltd
> > > >> > Wellington, New Zealand ______________________________**
> > > >> **_________________
> > > >>
> > > >
> > > > ______________________________**_________________
> > > > dba-SQLServer mailing list
> > > > dba-SQLServer@**databaseadvisors.com <
> > dba-SQLServer at databaseadvisors.com
> > > >
> > > > http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> > > > http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
> > > >
> > > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> > >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>