Re: [sqlite] "Stored procedures" performance issue

[Igor Tandetnik]

Darren Duncan wrote:
> Igor Tandetnik wrote:
>> Kristoffer Danielsson
>>  wrote:
>>> When I create my own "stored procedures" using
>>> sqlite3_create_function, I get horrible performance (which I
>>> expected) even though the column of interest is INDEXED.
>>>
>>> Consider this sample (it's stupid, but it shows my problem):
>>>
>>> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>>>
>>> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside
>>> this function, if I encounter a date greater than my birthday, then
>>> I want to tell SQLite to stop searching, since the date is indexed.
>>
>> I'm not sure what you mean by "a date greater than my birthday".
>> Presumably, your birthday comes every year, so every date is either
>> your birthday or falls between two birthdays (except dates before
>> the date
>> you were actually born on, but those can't be greater than any of
>> your
>> birthday dates).
>
> Another meaning for birthday is the the day in history where one was
> born, and there is just one of these per person.  More often this is
> what people are
> talking about when they are dealing with dates having a year part.

With this interpretation, having a function IS_MY_BIRTHDAY(IndexedDate) 
doesn't make much sense. In any case, one part of my response deals with 
this possibility.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Darren Duncan]

Igor Tandetnik wrote:
> Kristoffer Danielsson  wrote:
>> When I create my own "stored procedures" using
>> sqlite3_create_function, I get horrible performance (which I
>> expected) even though the column of interest is INDEXED.
>>
>> Consider this sample (it's stupid, but it shows my problem):
>>
>> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>>
>> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside
>> this function, if I encounter a date greater than my birthday, then I
>> want to tell SQLite to stop searching, since the date is indexed.
> 
> I'm not sure what you mean by "a date greater than my birthday". 
> Presumably, your birthday comes every year, so every date is either your 
> birthday or falls between two birthdays (except dates before the date 
> you were actually born on, but those can't be greater than any of your 
> birthday dates).

Another meaning for birthday is the the day in history where one was born, and 
there is just one of these per person.  More often this is what people are 
talking about when they are dealing with dates having a year part. -- Darren 
Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Kees Nuyt]

On Mon, 5 Oct 2009 18:01:46 +0200, Kristoffer Danielsson
 wrote:

> This makes sense. Though, I think the 
> documentation should cover this.

Much of this is implicitly or explicitly covered in
http://www.sqlite.org/optoverview.html .

And what Scott Hess said.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Scott Hess]

Not to be flippant, but how could SQLite optimize for the output of a
custom function you define outside of SQLite?  It can't possibly know
anything about how IS_MY_BIRTHDAY() works.  Your function could return
1 for every single row, or for no rows at all, without regard to
whether you're passing the function an indexed column.

-scott


On Mon, Oct 5, 2009 at 9:01 AM, Kristoffer Danielsson
<kristoffer.daniels...@live.se> wrote:
>
> This makes sense. Though, I think the documentation should cover this.
>
>
>
> Thanks for your response.
>
>> From: paiva...@gmail.com
>> Date: Mon, 5 Oct 2009 09:31:10 -0400
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] "Stored procedures" performance issue
>>
>> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
>> > function, if I encounter a date greater than my birthday, then I want to 
>> > tell SQLite to stop searching, since the date is indexed.
>> >
>> > Is this possible? If so, how?
>>
>> Even if this was possible it would be useless because you cannot say
>> if SQLite traverses table via index or makes just full table scan. And
>> in your particular case SQLite will see that there's no benefit in
>> using any index and will just scan the whole table.
>> The general advice in this case is to add column to the table which
>> will store the result of your function, create an index on that
>> additional column and then use that column in the query. In this case
>> SQLite will understand that it's better to use index and will use it.
>>
>> Pavel
>>
>> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
>> <kristoffer.daniels...@live.se> wrote:
>> >
>> > When I create my own "stored procedures" using sqlite3_create_function, I 
>> > get horrible performance (which I expected) even though the column of 
>> > interest is INDEXED.
>> >
>> >
>> > Consider this sample (it's stupid, but it shows my problem):
>> >
>> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>> >
>> >
>> >
>> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
>> > function, if I encounter a date greater than my birthday, then I want to 
>> > tell SQLite to stop searching, since the date is indexed.
>> >
>> >
>> >
>> > Is this possible? If so, how?
>> >
>> >
>> > Thanks.
>> >
>> > _
>> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och 
>> > Digg när de skickar e-post till dig.
>> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _
> Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> när de skickar e-post till dig.
> http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Kristoffer Danielsson]

This makes sense. Though, I think the documentation should cover this.

 

Thanks for your response.
 
> From: paiva...@gmail.com
> Date: Mon, 5 Oct 2009 09:31:10 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "Stored procedures" performance issue
> 
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> > Is this possible? If so, how?
> 
> Even if this was possible it would be useless because you cannot say
> if SQLite traverses table via index or makes just full table scan. And
> in your particular case SQLite will see that there's no benefit in
> using any index and will just scan the whole table.
> The general advice in this case is to add column to the table which
> will store the result of your function, create an index on that
> additional column and then use that column in the query. In this case
> SQLite will understand that it's better to use index and will use it.
> 
> Pavel
> 
> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > When I create my own "stored procedures" using sqlite3_create_function, I 
> > get horrible performance (which I expected) even though the column of 
> > interest is INDEXED.
> >
> >
> > Consider this sample (it's stupid, but it shows my problem):
> >
> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
> >
> >
> >
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> >
> >
> > Is this possible? If so, how?
> >
> >
> > Thanks.
> >
> > _
> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> > när de skickar e-post till dig.
> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Pavel Ivanov]

> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> function, if I encounter a date greater than my birthday, then I want to tell 
> SQLite to stop searching, since the date is indexed.
>
> Is this possible? If so, how?

Even if this was possible it would be useless because you cannot say
if SQLite traverses table via index or makes just full table scan. And
in your particular case SQLite will see that there's no benefit in
using any index and will just scan the whole table.
The general advice in this case is to add column to the table which
will store the result of your function, create an index on that
additional column and then use that column in the query. In this case
SQLite will understand that it's better to use index and will use it.

Pavel

On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
 wrote:
>
> When I create my own "stored procedures" using sqlite3_create_function, I get 
> horrible performance (which I expected) even though the column of interest is 
> INDEXED.
>
>
> Consider this sample (it's stupid, but it shows my problem):
>
> SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
>
>
>
> IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> function, if I encounter a date greater than my birthday, then I want to tell 
> SQLite to stop searching, since the date is indexed.
>
>
>
> Is this possible? If so, how?
>
>
> Thanks.
>
> _
> Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> när de skickar e-post till dig.
> http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Stored procedures" performance issue

[Kristoffer Danielsson]

When I create my own "stored procedures" using sqlite3_create_function, I get 
horrible performance (which I expected) even though the column of interest is 
INDEXED.

 
Consider this sample (it's stupid, but it shows my problem):

SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); 

 

IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
function, if I encounter a date greater than my birthday, then I want to tell 
SQLite to stop searching, since the date is indexed.

 

Is this possible? If so, how?


Thanks.
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users