you can skip the loading of that column using deferred(), for example. still, 5000 * 8 is only 40K. should be well under 200 ms.
On Feb 25, 2010, at 4:10 AM, dusans wrote: > News has a TEXT column [content] which is in average about 5000 in > length. > So ye, u found the problem :) > > And how to overcome (workaround) this in sqlalchemy? > > On 25 feb., 00:42, Michael Bayer <[email protected]> wrote: >> On Feb 24, 2010, at 6:15 PM, dusans wrote: >> >> >> >> >> >>> Hi. I got this simple query thats running very slow: >>> Im working on sqlalchemy 0.6 0.6beta1, sqlite3, python 2.5, windows xp >> >>> 1. I've addet News.[title] to make it run faster, but its still very >>> very slow >> >>> == Code (10 seconds) == : >>> Session.query(News.title, KernelResults).\ >>> select_from(join(News, KernelResults, >>> News.news_id == KernelResults.news_id_2)).\ >>> filter(KernelResults.news_id_1 == >>> c.news_id).\ >>> filter(KernelResults.kernel_id == 0).\ >>> order_by(desc(KernelResults.similarity))[: >>> 8] >> >>> == The SQL (50 miliseconds) ==: >>> SELECT >>> news.title AS news_title, >>> kernel_results.id_row AS kernel_results_id_row, >>> kernel_results.news_id_1 AS >>> kernel_results_news_id_1, >>> kernel_results.news_id_2 AS kernel_results_news_id_2, >>> kernel_results.similarity AS kernel_results_similarity, >>> kernel_results .kernel_id AS kernel_results_kernel_id >>> FROM news >>> JOIN kernel_results >>> ON news.news_id = kernel_results.news_id_2 >>> WHERE kernel_results.news_id_1 = 66 >>> AND kernel_results.kernel_id = 0 >>> ORDER BY kernel_results.similarity DESC >>> LIMIT 8 OFFSET 0 >> >>> Could someone please help me? im still fresh with sqlalchemy :) >> >> there's nothing apparent which would cause such slowness unless you have a >> high degree of network overhead when your SQLAlchemy application runs, or >> for example one or more of the columns returned represents an enormous text >> or binary field, perhaps. For 8 rows and a 50ms query the total time spent >> should be in the ballpark of well under 100 ms. >> >> >> >> >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group >>> athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
