Tag Info

New answers tagged

6

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


2

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


0

Great dezso, it works! Here is the final version of my function: CREATE OR REPLACE FUNCTION fnct_clear_temp_fields() RETURNS VOID AS $$ DECLARE dataset_1_row RECORD; --Record variable to go through each row of the view below update_query TEXT; --The dynamic UPDATE query to be executed BEGIN FOR dataset_1_row IN --Cycle through rows of query ...


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


4

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function. Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. To change this back to the default, you need to find out what isolation level is currently set (before changing it above). This can ...


0

I can't answer to question 1 as I do not have enough information about the MySQL instances offered by different hosting providers. To answer the rest of your questions, yes, if you can leverage the features of the RDBMS you're using, you might replace PHP code with SQL scripts that can run faster than your code. Just think about the time you're saving up by ...


0

I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering Craig's suggestion) you are done.


1

On the scale of a kilometer the difference between a geoid & a flat plane ought to be negligible. (ok, unless you are very close to the poles I suppose) Couldn't you just use a planer geometry distance formula? i.e. x^2 + y^2 < a^2 That might be much cheaper than so many trig calls? Another trick that comes to mind is using some sort of hash. e.g. ...


3

To expand on @RobFarley's answer, by storing spatial data as Lat, Long AND GEOGRAPHY datatype, you can perform much faster distance calculations, without any of the nested cos/sin/radians functions. There is a good example and tutorial on how to using the Spatial functions in SQL on MSSQLTips. My code below is looking for all locations saved in my database ...


2

If you are looking for fast query performance, and you "don't require a large amount of accuracy" then perhaps don't try looking for things within a circle centered on your location, but look for them in a square centered on your location. You didn't explain your use-case, but perhaps this might give a tolerable result. The calculation required is a simple ...


5

Create a computed column which converts your lat and long columns into a geography type using the Point constructor. Then put a spatial index on this computed column. Then your query can create a geography point from your circle centre, and compare distances. Should be very quick.


4

Scalar valued functions takes time to invoke and by the looks of it you are doing quite a few calls to CIRCLEDISTANCE. You could rewrite your function to a inline table valued function instead. create function CIRCLEDISTANCE ( @LAT1 varchar(250), @LAT2 varchar(250), @LNG1 varchar(250), @LNG2 varchar(250) ) returns table as return ( select ...



Top 50 recent answers are included