Mark Breen
marklbreen at gmail.com
Wed Aug 31 10:56:22 CDT 2011
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
>
>